oracle statement to calculate concurrent phone call peak in db

does each record mean a port is 'consumed'? (i.e. busy)
are those duration units seconds?
is there any significance to be drawn from having the data presented in 2 chunks? (I assume no)

{+edit sorry}

Yes each record is a port, and the duration is in seconds, sorry i should have specified that.

thanks
Dave

Paul,

thank you this is great. i have a questioni for you though. im only getting hours 0100-0205, is there something restricting it to that time frame? i have records for full 24 hour period. i changed everything i think i needed to change, i changed the table "calls" to my actual schema.tablename and then the dates. see below please what i changed, then i have my output below that.

thanks
Dave

with
hrs as (
            select rownum as hr
            from dual
            connect by level <= 24
           )
, mins as (
            select rownum - 1 as min
            from dual
            connect by level <= 59
          )
, range as (
             select
                 substr('0' || hr,-2) || substr('0' || min, -2) as hrmin
             , to_date('05-06-2013','dd-mm-yyyy') + hr/24 + min/(24*60) pstart
             from hrs
             cross join mins
           )
select
   range.hrmin
 , sum(1) busy_ports
from range
inner join (
            select
              CALL_DATE_TIME_K as starts
            , CALL_DATE_TIME_K + INBOUND_DURATION/86400 as ends
            , INBOUND_DURATION as du
            from alliance.master_call
            where CALL_DATE_TIME_K >= to_date('04-06-2013','dd-mm-yyyy')
              and CALL_DATE_TIME_K <  to_date('05-06-2013','dd-mm-yyyy')
           ) c on range.pstart between c.starts and c.ends
group by
     range.hrmin
order by
     range.hrmin



OUTPUT:

HRMIN,BUSY_PORTS
0100,37
0101,35
0102,33
0103,32
0104,28
0105,28
0106,26
0107,25
0108,24
0109,24
0110,24
0111,23
0112,23
0113,23
0114,21
0115,20
0116,20
0117,19
0118,17
0119,17
0120,16
0121,16
0122,15
0123,15
0124,15
0125,14
0126,14
0127,13
0128,13
0129,13
0130,13
0131,13
0132,11
0133,11
0134,10
0135,10
0136,10
0137,10
0138,9
0139,9
0140,9
0141,8
0142,8
0143,7
0144,6
0145,6
0146,4
0147,4
0148,4
0149,4
0150,4
0151,4
0152,4
0153,3
0154,3
0155,1
0156,1
0157,1
0158,1
0200,1
0201,1
0202,1
0203,1
0204,1
0205,1

The problem i had was the date in this row:

, to_date('05-06-2013','dd-mm-yyyy') + hr/24 + min/(24*60) pstart

I changed it to the 04-06-2013 and it worked perfect

thank you so much.
Thanks
Dave

Great help, Thanks

How would i output just the line for the highest busyport?

Dave

Dave,

Hi. A couple of things.

The query above is biased to call durations that will span a change of minute, so short calls that start and finish within one of the minute slots is ignored. To compensate for that bias I have included a change (see lines 37,38) where I have forced duration to a minimum of 60 seconds. This may also produce a small bias in that a short call would now be counted in at least 2 of the minute slots. If you believe this to be a problem let me know and I'll see if I can improve the approach.

I have also introduced a method to select just the highest busy minute, or a set of busy minutes, just use the calculated 'row_ref' to choose what you want here.

Does it look like you need more ports?

with 
hrs as (
            select rownum as hr 
            from dual
            connect by level <= 24
           )
, mins as (
            select rownum - 1 as min 
            from dual
            connect by level <= 59
          )
, range as (
             select
                 substr('0' || hr,-2) || substr('0' || min, -2) as hrmin
             , to_date('15-04-2013','dd-mm-yyyy') + hr/24 + min/(24*60) pstart
             from hrs
             cross join mins
           )
select
  hrmin
, busy_ports
, row_ref
from (
    select
      hrmin
    , busy_ports
    , row_number() over (order by busy_ports DESC) as row_ref
    from (
          select
             range.hrmin
           , sum(1) busy_ports
          from range
          inner join (
                      select
                        CALL_DATE_TIME_K as starts
            
                      --, CALL_DATE_TIME_K + INBOUND_DURATION/86400 as ends
                      , CALL_DATE_TIME_K + greatest(INBOUND_DURATION/86400, 60/86400) as ends
            
                      , INBOUND_DURATION as du
                      from calls
                      where CALL_DATE_TIME_K >= to_date('15-04-2013','dd-mm-yyyy')
                        and CALL_DATE_TIME_K <  to_date('16-04-2013','dd-mm-yyyy')
                     ) c on range.pstart between c.starts and c.ends
          group by
               range.hrmin
         )
    )
where 
 row_ref = 1 -- for just the highest
-- row_ref < 21 -- e.g. for the busiest periods

Select allOpen in new window

see http://sqlfiddle.com/#!4/7a273/18

OOOPS
I have had a real "doh!" moment....
My range calculations have been incorrect, PLEASE replace the query with this corrected one (changes in lines 3-10)

with 
hrs as (
            select rownum - 1 as hr 
            from dual
            connect by level <= 24
           )
, mins as (
            select rownum - 1 as min 
            from dual
            connect by level <= 60
          )
, range as (
             select
                 substr('0' || hr,-2) || substr('0' || min, -2) as hrmin
             , to_date('15-04-2013','dd-mm-yyyy') + hr/24 + min/(24*60) pstart
             from hrs
             cross join mins
           )
select
  hrmin
, busy_ports
, row_ref
from (
    select
      hrmin
    , busy_ports
    , row_number() over (order by busy_ports DESC) as row_ref
    from (
          select
             range.hrmin
           , sum(1) busy_ports
          from range
          inner join (
                      select
                        CALL_DATE_TIME_K as starts
            
                      --, CALL_DATE_TIME_K + INBOUND_DURATION/86400 as ends
                      , CALL_DATE_TIME_K + greatest(INBOUND_DURATION/86400, 60/86400) as ends
            
                      , INBOUND_DURATION as du
                      from calls
                      where CALL_DATE_TIME_K >= to_date('15-04-2013','dd-mm-yyyy')
                        and CALL_DATE_TIME_K <  to_date('16-04-2013','dd-mm-yyyy')
                     ) c on range.pstart between c.starts and c.ends
          group by
               range.hrmin
         )
    )
where
 row_ref = 1 -- for just the highest
-- row_ref < 21 -- e.g. for the busiest periods      

Select allOpen in new window

see: http://sqlfiddle.com/#!4/7a273/25

range now has 1440 records, min '0000', max '2359' (as it should be)

Paul,

Thank you so much, this report is a life saver for me. I appreciate all the help

Thanks
Dave