crosstab like query - flip table on its side

[dpimental]

All, I have a table that lists orders and which stage of production processing they have gone through and a create date for each record, like so

order # stage create date
1234 StartProofing 3/29/2011
1235 StartProofing 3/29/2011
1234 EndProofing 3/30/2011
1234 ProofApproved 4/1/2011
1236 StartProofing 3/29/2011
1237 StartProofing 3/29/2011
1235 EndProofing 3/30/2011
1235 ProofApproved 3/31/2011

What I want to do is show each order, the date for StartProofing, the date for EndProofing and the date for ProofApproved, like so

order # SPDate EPDate PADate
1234 3/29/2011 3/30/2011 4/1/2011
1235 3/29/2011 3/30/2011 3/31/2011

Can this be done as a crosstab? A Union?
It all comes from the same table.

David Pimental
(US, Oh)

 

[PHV]

A double SELF JOIN should suffice:

SELECT S.[order #], S.[create date] AS SPDate, E.[create date] AS EPDate, P.[create date] AS PADate
FROM (yourTable S
INNER JOIN yourTable E ON S.[order #] = E.[order #])
INNER JOIN yourTable P ON S.[order #] = P.[order #]
WHERE S.stage = 'StartProofing' AND E.stage = 'EndProofing' AND P.stage = 'ProofApproved'

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[PWise]

Wont't this do

TRANSFORM Min(Createdate) AS MinOfsdate
SELECT order
FROM TRANSFORM Min(Createdate) AS MinOfsdate
SELECT order
FROM Production
GROUP BY Production.order
PIVOT Status;

GROUP BY Production.order
PIVOT Status;

Ph:
will your inner join query work on order that are not completed
perhaps use left joins

 

[PHV]

will your inner join query work on order that are not completed
No, as asked ...

 

[dpimental]

They need to be left joins, since there'll be a mix of how many stages each order has been through.

Does that make sense?

David Pimental
(US, Oh)

 

[PWise]

what i would use

Select Distinct ordernum,StartProofing.ordernum as StartProofing,
EndProofing.ordernum as EndProofing,
ProofApproved.ordernum as ProofApproved
from Production
left join (Select ordernum,Createdate
from Production
where stage="StartProofing"
)as StartProofing
on StartProofing.ordernum = Production.ordernum
left join (Select ordernum,Createdate
from Production
where stage="EndProofing"
)as EndProofing
on EndProofing.ordernum = Production.ordernum
left join (Select ordernum,Createdate
from Production
where stage="ProofApproved"
)as ProofApproved
on ProofApproved.ordernum = Production.ordernum

 

[PHV]

David, the real question is if you'll admit null value for any of the SPDate, EPDate or PADate column.
Assuming that SPDate is mandatory but not EPDate nor PADate:

SELECT S.[order #], S.[create date] AS SPDate, E.EPDate, P.PADate
FROM (yourTable S
LEFT JOIN (SELECT [order #],[create date] AS EPDate FROM yourTable WHERE stage='EndProofing'
) E ON S.[order #] = E.[order #])
LEFT JOIN (SELECT [order #],[create date] AS PADate FROM yourTable WHERE stage='ProofApproved'
) P ON S.[order #] = P.[order #]
WHERE S.stage = 'StartProofing'

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886