Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Mailing Lists -> Oracle-L -> 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) );
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:38:37 CDT
![]() |
![]() |