| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: An SQL question , not easy ;-)
No - if you draw a diagram showing the 4 calls given in the original test data, you can see clearly that at no time are there more than 3 calls in progress simultaneously. Unless you're using some strange definition of the word "simultaneous" of which I'd previously been unaware...
> -----Original Message-----
> From: Thomas, Kevin [mailto:Kevin.Thomas_at_calanais.com]
> Sent: 15 August 2001 16:11
> To: Multiple recipients of list ORACLE-L
> Subject: RE: An SQL question , not easy ;-)
>
>
> Is this not flawed in that given these results:
>
> eventtime event
> -------------------- -----
> 01-AUG-2001 12:10:00 start > add 1 - 1
> 01-AUG-2001 12:15:00 start > add 1 - 2
> 01-AUG-2001 12:25:00 start > add 1 - total 3
> 01-AUG-2001 12:30:00 end < subtract 1 - 2
> 01-AUG-2001 12:40:00 end < subtract 1 - 1
> 01-AUG-2001 12:45:00 start > add 1 - 2
> 01-AUG-2001 12:47:00 end < subtract 1 - 1
> 01-AUG-2001 12:55:00 end < subtract 1 - 0
>
> The highest total is 3 this is incorrect by 1. There have
> been a total of 4
> simultaneous calls
> 3 of which were simultaneous and 1 which ran simultaneously
> with the one of
> the three calls!
>
> IMHO
> Kev.
>
> still working on a solution....here's what I have so
> far...lot's of dbms
> outputs so it may look very confusing.
> Based on the data that Andrey first provided.
>
> based on the table;
>
> create table phone
> ( call_start date
> ,call_end date
> )
> /
>
>
>
> -----Original Message-----
> Sent: 15 August 2001 15:22
> To: Multiple recipients of list ORACLE-L
>
>
> Note that whenever a call starts, this increases the current
> number of calls
> by 1, and whenever a call ends, this decreases the current
> number of calls
> by 1. Hence you can "uncouple" the start and end times - you
> don't need to
> know that a given start time and a given end time belong to
> the same call!
> (Think about it - it's a bit counterintuitive, but it's true!).
>
> So:
>
> SELECT CALL_START_DATE "eventtime", 'start' "eventtype" FROM calltable
> UNION ALL
> SELECT CALL_END_DATE "eventtime", 'end' "eventtype" FROM calltable
> ORDER BY 1
>
> Then you set a "current number of calls" variable to zero,
> and move the
> cursor through the results, adding 1 to the variable for each
> 'start' and
> subtracting 1 for each 'end'. Whenever you add 1, if the
> current number of
> calls is higher than its highest value so far, store the new
> "highest value
> so far". When you've finished, this latter variable will
> contain the maximum
> number of simultaneous conversations.
>
> Hope this helps.
>
> Paul
>
> ------------------------------------------------------------
> Paul Vincent
> Database Administrator, University of Central England
> ------------------------------------------------------------
>
> > -----Original Message-----
> > From: Andrey Bronfin [mailto:andreyb_at_elrontelesoft.com]
> > Sent: 15 August 2001 11:21
> > To: Multiple recipients of list ORACLE-L
> > Subject: An SQL question , not easy ;-)
> >
> >
> > Dear gurus !
> > I have a table of phone calls , 2 fields : CALL_START DATE
> > , CALL_END
> > DATE .
> > I need an SQL statement or a PL/SQL block to calculate the
> > maximum number of
> > SIMULTANIOUS phone conversations.
> > Please help !!!
> > Thanks a lot in advance !
> > Andrey.
> >
> >
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.com
> > --
> > Author: Andrey Bronfin
> > INET: andreyb_at_elrontelesoft.com
> >
> > Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> > San Diego, California -- Public Internet access /
> Mailing Lists
> > --------------------------------------------------------------------
> > To REMOVE yourself from this mailing list, send an E-Mail message
> > to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> > the message BODY, include a line containing: UNSUB ORACLE-L
> > (or the name of mailing list you want to be removed from). You may
> > also send the HELP command for other information (like subscribing).
> >
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Paul Vincent
> INET: Paul.Vincent_at_uce.ac.uk
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>
>
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Paul Vincent INET: Paul.Vincent_at_uce.ac.uk Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).Received on Thu Aug 16 2001 - 02:03:01 CDT
 |
 |