RE: An SQL question , not easy ;-)

From: Jon Walthour <jonw_at_fuse.net>
Date: Wed, 15 Aug 2001 10:55:05 -0700
Message-ID: <F001.0036B8F8.20010815101057@fatcity.com>

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" <David.Lord_at_hayscsg.com>
> Date: 2001/08/15 Wed AM 11:01:33 EDT
> To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> 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: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: Jon Walthour
  INET: jonw_at_fuse.net

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 - 12:55:05 CDT