Pivot Table Issue 2

[mptwoadmin]

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

 

[simian336]

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

 

[mptwoadmin]

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?

 

[gmmastros]

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

 

[simian336]

George is correct, in my example MN is and integer and thus would be

and d1.mn = 14


Simi

 

[gmmastros]

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

 

[mptwoadmin]

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

 

[gmmastros]

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

 

[mptwoadmin]

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

 

[gmmastros]

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

 

[gmmastros]

using your sample data....

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

 

[mptwoadmin]

Awesome thanks for everything.. Works great.. Both of you.. your qry's worked well but gmmastros's worked best for my situation..