RE: An SQL question , not easy ;-)

Hi David !
It's perfectly fine : four calls started during the period of the first call , but the maximum of 2 were carried out simultaneously . Your PL/SQL block addresses this , since U subtract 1 from the current number of concurrent calls each time the second person hangs up. So , the l_max_count is never more than 2.
Anyway , your query perfectly suited my needs. (My actual problem was slightly more complicated then the one i posted , but U gave me a bright idea on how to deal with it).
Thanks a lot to all the oracle DBAs brotherhood.

-----Original Message-----
Sent: Thursday, August 16, 2001 10:36 AM To: Multiple recipients of list ORACLE-L

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:greg.solomon_at_betfair.com]
> 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:Kevin.Thomas_at_calanais.com]
> > 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: David.Lord_at_hayscsg.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: Greg Solomon
> INET: greg.solomon_at_betfair.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: Lord, David - C&S
  INET: David.Lord_at_hayscsg.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: 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).