mptwoadmin
Programmer
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
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