Re: SQL question

From: Trysql <trysql_at_aol.com>
Date: 28 Mar 1999 09:06:26 GMT
Message-ID: <19990328040626.28819.00001051@ng-cr1.aol.com>

Greetings All,
Looks like just another pivot query to me.With a group by and aggregate no need to worry about outer joins.

SELECT b.event# ,

Sum(Decode(a.event,'BKD',a.eventdate-b.eventdate)) as BKD_PKD,
Sum(Decode(a.event,'OBF',a.eventdate-b.eventdate)) as OBF_PKD,
Sum(Decode(a.event,'DOT',a.eventdate-b.eventdate)) as DOT_PKD

FROM event_table a,event_table b
WHERE b.event#=a.event#
AND b.eventdate=(SELECT Min(c.eventdate) FROM event_table c WHERE c.event#=b.event# AND c.eventdate<a.eventdate) GROUP BY b.event#
ORDER BY 1; Steve Dassin

> usmrall <srallapally_at_netscape.net>writes:
>Hi all,
>I have a table or events which looks like this
>Event# Event EventDate EventText
>100 PKD 199812010745 Picked up cargo
>100 BKD 199812152100 Booked Cargo
>100 OBF 199812160600 On board flight
>100 DOT 199812171300 Delivered to Trucker
>I am trying to get a report which lists the date difference between the
>dates for PKD and BKD, PKD and OBF, PKD and DOT events like
>Event (PKD - BKD) (PKD - OBF) (PKD - DOT)
>--------------------------------------------
>100 ??? ??? ???
>I was wondering if the more SQL-enlightened of you could help me out. Also,
>how can we handle time subtraction in Oracle ? By using trunc, I can get date
>differences, but I need time differences too.
>tia
>sanjay
Received on Sun Mar 28 1999 - 03:06:26 CST