Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Pivot Table Issue 2

Status
Not open for further replies.

mptwoadmin

Programmer
May 15, 2006
46
US
Hi I am having difficulty today with my SQL pivot.

My current data looks like this.

datetime machine number status
3/26/13 7:46 14 1
3/26/13 7:47 14 0
3/26/13 7:48 13 1
3/26/13 7:49 13 0
3/26/13 7:50 7 1
3/26/13 7:52 7 0


I am attempting to format data like this.

machine start fill end fill
14 3/26/13 7:46 3/26/13 7:47
13 3/26/13 7:48 3/26/13 7:49
7 3/26/13 7:50 3/26/13 7:52


My qry =

SELECT name,
min(CASE WHEN value = 1 THEN datetime ELSE NULL END) AS "FStart",
max(CASE WHEN value = 0 THEN datetime ELSE NULL END) AS "FEnd"
from table
where col like 'machine%'
and datetime > {ts '2013-03-26 07:45:00'}
and datetime <= {ts '2013-03-26 15:45:00'}
GROUP BY name

I am only returning one record instead of many many records.. Can someone explain what I am doing wrong?

Thank You
 
Well, This is not a pivot and it only works with 2 records... but should be fast.

Try it and see what you think.

create table dttest (dt datetime, mn integer, [stat] integer)

insert into dttest values ('3/26/13 7:46', 14, 1)
insert into dttest values (' 3/26/13 7:47', 14, 0)
insert into dttest values ('3/26/13 7:48', 13, 1)
insert into dttest values ('3/26/13 7:49', 13, 0)
insert into dttest values ('3/26/13 7:50', 7, 1)
insert into dttest values ('3/26/13 7:52', 7, 0)

select d1.mn, d1.dt, d2.dt
from dttest d1
join dttest d2
on d1.mn =d2.mn
and d1.stat <> d2.stat
where d1.stat=1


Simi
 
Simi you qry works well with your prefilled data..

If I need to add a parameter to view a certain "MN" I am unable..

select d1.mn, d1.dt, d2.dt
from dttest d1
join dttest d2
on d1.mn =d2.mn
and d1.stat <> d2.stat
where d1.stat=1
and d1.mn like '%14%' <--- Adding this does not work..

any suggestions?

 
What is the data type for the mn column?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
George is correct, in my example MN is and integer and thus would be

and d1.mn = 14


Simi
 
Actually... it does work. The problem must be something else that mptwoadmin is not showing.

SQL Server will convert the int column to a string so it can perform the like comparison.

Of course, like '%14%' will match on 140 and 214 also.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Yes it does work with and d1.mn = 14..I got the qry to work the error was on my part sorry..

But..

What I am seing is this .. When duplicate mn's occure the qry fails..How do I over come this. There will be multiple mn's.

create table #dttest (dt datetime, mn integer, [stat] integer)

insert into #dttest values ('2013-03-26 07:46:07', 07, 1)
insert into #dttest values ('2013-03-26 07:47:49', 07, 0)
insert into #dttest values ('2013-03-26 07:48:04', 13, 1)
insert into #dttest values ('2013-03-26 07:49:57', 13, 0)
insert into #dttest values ('2013-03-26 07:50:15', 07, 1)
insert into #dttest values ('2013-03-26 07:52:11', 07, 0)

select d1.mn, d1.dt, d2.dt
from #dttest d1
join #dttest d2
on d1.mn =d2.mn
and d1.stat <> d2.stat
where d1.stat=1

Thanks
 
Based on the same data you just posted, what output are you expecting?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
This is the expected result: if another dupe occurs in this case a dupe "#7"

mn dt dt
7 3/26/2013 7:46:07 3/26/2013 7:47:49
13 3/26/2013 7:48:04 3/26/2013 7:49:57
7 3/26/2013 7:50:15 3/26/2013 7:52:11
 
This becomes tricky now. Is there anything in your data that indicates which pair of rows go together? Maybe another column you are not showing?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
using your sample data....

Code:
create table #dttest (dt datetime, mn integer, [stat] integer)

insert into #dttest values ('2013-03-26 07:46:07', 07, 1)
insert into #dttest values ('2013-03-26 07:47:49', 07, 0)
insert into #dttest values ('2013-03-26 07:48:04', 13, 1)
insert into #dttest values ('2013-03-26 07:49:57', 13, 0)
insert into #dttest values ('2013-03-26 07:50:15', 07, 1)
insert into #dttest values ('2013-03-26 07:52:11', 07, 0)

; with Data As
(
  Select *, Row_Number() Over (Partition By mn, stat Order By dt) As RowId
  From	 #dttest
)
Select  A.mn, A.dt As StartFill, B.dt  as EndFill
From    Data As A
        Inner Join Data As B
          On  A.mn = b.mn
          And A.Stat = 1 
          and b.stat = 0
          and a.RowId = B.RowId

This does return the same output as your expected results, but there are possible issues with this. The biggest issue is... this assumes that every machine has a stat=1 row and a corresponding stat = 0 row. If you are missing any rows, this will not produce the results you are expecting.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Awesome thanks for everything.. Works great.. Both of you.. your qry's worked well but gmmastros's worked best for my situation..

 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top