| 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 ;-)
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-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 Wed Aug 15 2001 - 08:52:52 CDT
 |
 |