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: '[EMAIL PROTECTED]'
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).
