Sorry to resurrect this thread and pick holes, but I was thinking about this
query on the way home last night (sad, I know) and came across a problem
with it. It works fine with the example we have been using, but only
because none of the calls has more than one *non-overlapping* call start
during it.
If I understand the query correctly, it's selecting the number of calls
started, including itself, during each call. But, say you have two
telephones. One person makes one long call on the first. Meanwhile,
someone else makes three short calls on the other. The query would show
four call starts during the first call (itself plus the three on the other
phone), but only two are ever in progress at any one time.
Nice problem
David Lord
> -----Original Message-----
> From: Greg Solomon [mailto:[EMAIL PROTECTED]]
> Sent: 15 August 2001 16:46
> To: Multiple recipients of list ORACLE-L
> Subject: RE: An SQL question , not easy ;-)
>
>
> Or use a self-join
>
> select a.call_start, count(b.call_start) interruptions
> from phone_call a, phone_call b
> where a.call_start <= b.call_start and a.call_end < b.call_start
> group by a.call_start
>
> -----Original Message-----
> Sent: 15 August 2001 16:02
> To: Multiple recipients of list ORACLE-L
>
>
> Yes, so the maximum is 3, between 12:25 and 12:30. To
> explicitly show the
> maximum (and a little histogram) you could amend it as follows: -
>
> declare
> l_count pls_integer := 0;
> l_max_count pls_integer := 0;
> begin
> for rec in (
> select call_start time, 1 incr from table
> union all
> select call_end time, -1 incr from table
> order by 1
> ) loop
> l_count := l_count + rec.incr;
> if( l_count > l_max_count ) then
> l_max_count := l_count;
> end if;
> dbms_output.put_line( to_char(rec.time) || ' -
> ' || l_count
> || ' ' || lpad('*',l_count) )
> end loop;
> dbms_output.put_line( 'Maximum concurrent calls = ' ||
> to_char(l_max_count) );
> end;
>
> Regards
> David Lord
>
> > -----Original Message-----
> > From: Thomas, Kevin [mailto:[EMAIL PROTECTED]]
> > Sent: 15 August 2001 15:07
> > To: Multiple recipients of list ORACLE-L
> > Subject: RE: An SQL question , not easy ;-)
> >
> >
> > David,
> >
> > Here's the output from your suggestion based on a table
> > with the following rows;
> >
> > CALL_START CALL_END
> > ----------------- -----------------
> > 01-AUG-2001 12:10 01-AUG-2001 12:40
> > 01-AUG-2001 12:15 01-AUG-2001 12:30
> > 01-AUG-2001 12:25 01-AUG-2001 12:55
> > 01-AUG-2001 12:45 01-AUG-2001 12:47
> >
> > ---------------------
> >
> > 01-AUG-2001 12:10 - 1
> > 01-AUG-2001 12:15 - 2
> > 01-AUG-2001 12:25 - 3
> > 01-AUG-2001 12:30 - 2
> > 01-AUG-2001 12:40 - 1
> > 01-AUG-2001 12:45 - 2
> > 01-AUG-2001 12:47 - 1
> > 01-AUG-2001 12:55 - 0
> >
> >
> >
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Lord, David - C&S
> INET: [EMAIL PROTECTED]
>
> 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: [EMAIL PROTECTED] (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: Greg Solomon
> INET: [EMAIL PROTECTED]
>
> 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: [EMAIL PROTECTED] (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: Lord, David - C&S
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
