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
>

This is a logically valid solution to the problem, but performance of such solution will be less then satisfactory. If I'm not mistaken, the solution developed here is a software package for a shipping company, which can have literally thousands of events daily. "view on the fly", multiplied by 4 will kill that query, unless we are talking about 4 way SUN E10000 cluster. The only proper solution would be to redefine the underlying data model and separate the main events into separate tables. Performance begins with the data model. The alternative solution which would significantly speed up Jurij's query would be to create bitmap index on the 'EVE' column.
Regards,

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:40:17 CST