| 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 ;-)
I agree with you on that one, but what I was saying, (and I posted this in an earlier message) was that I interpreted the intial email wrongly. I was looking at:
therefore I was getting the answer, ok, there are 3 calls simultaneously 12:10, 12:15 and 12:25 but then there is also a simultaneous call going on between 12:25 and 12:45 (because the 12:25 call didn't end until 12:55), so I'm not totally mad! What I didn't pick up from the original email was that Andrey was purely looking for the MAX number of simultaneous calls which as you rightly point out was 3...!
Damn this lag!!
Kev.
-----Original Message-----
Sent: 16 August 2001 08:55
To: Multiple recipients of list ORACLE-L
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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Thomas, Kevin INET: Kevin.Thomas_at_calanais.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).Received on Thu Aug 16 2001 - 03:19:21 CDT
 |
 |