I think I have an alternative solution that, if you're using 8.1.6 or better, would
provide a pure SQL solution. It's probably not the tidiest SQL, but it works (as far
as I can tell):
SELECT c1 AS start_time
, c2 AS end_time
, total
FROM (SELECT LAG (call_time, 1) OVER (ORDER BY call_time) AS c1
, call_time AS c2
, incr
, SUM (incr) OVER (ORDER BY call_time RANGE UNBOUNDED PRECEDING) AS total
FROM (SELECT TO_CHAR (call_start, 'DD-MON-RR HH24:MI:SS') AS call_time
, 1 AS incr
FROM calls
UNION ALL
SELECT TO_CHAR (call_end, 'DD-MON-RR HH24:MI:SS') AS call_time
, -1 AS incr
FROM calls))
WHERE c1 IS NOT NULL
AND TO_DATE (c2, 'DD-MON-RR HH24:MI:SS') > TO_DATE (c1, 'DD-MON-RR HH24:MI:SS')
AND total = (SELECT MAX (total) AS max_sim_calls
FROM (SELECT LAG (call_time, 1) OVER (ORDER BY call_time) AS c1
, call_time AS c2
, incr
, SUM (incr) OVER (ORDER BY call_time RANGE UNBOUNDED
PRECEDING) AS total
FROM (SELECT TO_CHAR (call_start, 'DD-MON-RR HH24:MI:SS') AS
call_time
, 1 AS incr
FROM calls
UNION ALL
SELECT TO_CHAR (call_end, 'DD-MON-RR HH24:MI:SS') AS
call_time
, -1 AS incr
FROM calls)));
Jon Walthour
>
> From: "Lord, David - C&S" <[EMAIL PROTECTED]>
> Date: 2001/08/15 Wed AM 11:01:33 EDT
> To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
> Subject: RE: An SQL question , not easy ;-)
>
> 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: Jon Walthour
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).
