FW: An SQL question , not easy ;-)

From: Greg Solomon <greg.solomon_at_betfair.com>
Date: Wed, 15 Aug 2001 08:14:49 -0700
Message-ID: <F001.0036B4D4.20010815075052@fatcity.com>

oops, should be > not <

:o)

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

Cheers
Greg

-----Original Message-----
Sent: 15 August 2001 15:43
To: 'ORACLE-L_at_fatcity.com'

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).

Received on Wed Aug 15 2001 - 10:14:49 CDT