| 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 ;-)
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 - 201-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
> -----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-LReceived on Wed Aug 15 2001 - 10:45:28 CDT
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
 |
 |