Re: SQL question

From: Jurij Modic <jmodic_at_src.si>
Date: Sat, 27 Mar 1999 23:22:42 GMT
Message-ID: <36fd67f3.24061297@news.siol.net>

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 Received on Sat Mar 27 1999 - 17:22:42 CST