Re: SQL question

In article <36fd67f3.24061297_at_news.siol.net>,   jmodic_at_src.si (Jurij Modic) wrote:
> On Sat, 27 Mar 1999 17:40:38 GMT, usmrall <srallapally_at_netscape.net>
> wrote:
>
> >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.
>
> SCOTT_at_PO73> SELECT * FROM event_table;
>
> EVENT# EVE 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
>
> SCOTT_at_PO73> SELECT v1.event#,
> 2 (v2.eventdate - v1.eventdate) BKD_PKD,
> 3 (v3.eventdate - v1.eventdate) OBF_PKD,
> 4 (v4.eventdate - v1.eventdate) DOT_PKD
> 5 FROM
> 6 (SELECT event#,eventdate FROM event_table WHERE event='PKD') v1,
> 7 (SELECT event#,eventdate FROM event_table WHERE event='BKD') v2,
> 8 (SELECT event#,eventdate FROM event_table WHERE event='OBF') v3,
> 9 (SELECT event#,eventdate FROM event_table WHERE event='DOT') v4
> 10 WHERE v1.event# = v2.event#(+)
> 11 AND v1.event# = v3.event#(+)
> 12 AND v1.event# = v4.event#(+)
> 13 /
>
> EVENT# BKD_PKD OBF_PKD DOT_PKD
> ---------- ---------- ---------- ----------
> 100 14.5520833 14.9270833 16.21875
>
> The outer join operators ( "(+)" in a WHERE clause are used in case
> there are some events missing for a certain event#.
>
> >Also, how can we handle time subtraction in Oracle ? By using trunc, I can
get date
> >differences, but I need time differences too.
>
> As you can see from the above example, you simply subtract two dates
> to get the time difference between them. The unit for date arithmetics
> is 1 day, so the whole-number portion represents days, while decimal
> portion represent a time fraction of a day. You can use some basic
> arithmetics to get hours and minutes from the decimal part.
>
> >tia
> >sanjay
>
> HTH,
> Jurij Modic <jmodic_at_src.si>
> Certified Oracle7 DBA (OCP)
> ================================================
> The above opinions are mine and do not represent
> any official standpoints of my employer
>

Mladen Gogala

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Sun Mar 28 1999 - 14:33:36 CST