• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1071
  • Last Modified:

oracle statement to calculate concurrent phone call peak in db

Hello All,

Im not a programmer and but i mess around with oracle. Im trying to create a report that will the max concurrent calls for a day my systems has. I have a table in my DB that has a field called "call_date_time_k" and its a datetime field. then i have a "inbound_duration" so they would look like this:

CALL_DATE_TIME_K    INBOUND_DURATION
------------------- ----------------
2013-04-15 01:25:39                1
2013-04-15 01:25:40             1899
2013-04-15 01:25:40                9
2013-04-15 01:25:40               32
2013-04-15 01:25:40               71
2013-04-15 01:25:40                1
2013-04-15 01:25:40                2
2013-04-15 01:25:41              705
2013-04-15 01:25:41               40
2013-04-15 01:25:41                2
2013-04-15 01:25:42                2

CALL_DATE_TIME_K    INBOUND_DURATION
------------------- ----------------
2013-04-15 01:12:13             2913
2013-04-15 01:12:13             2485
2013-04-15 01:12:13              911
2013-04-15 01:12:14                2
2013-04-15 01:12:14               36
2013-04-15 01:12:15              121
2013-04-15 01:12:15               53
2013-04-15 01:12:15               88
2013-04-15 01:12:18             1146
2013-04-15 01:12:18                0
2013-04-15 01:12:18               78

This shows me when the phone call started (call_date_time_k) and how long it lasted (inbound_duration)

If i have 800 ports available for phone calls, i need to see how many phone calls during that 24 hour period did my system concurrently have up that was the max so i can determine if i need to add more ports or not.

is there a easy way i can calculate how many concurrent calls did the system peak at per hour based on this data?

if this is something that would not be easy , please let me know and i can just disregard this question.

Thanks in Advance,
Dave
0
WCGS_Operations
Asked:
WCGS_Operations
  • 6
  • 4
1 Solution
 
PortletPaulCommented:
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}
0
 
WCGS_OperationsAuthor Commented:
Yes each record is a port, and the duration is in seconds, sorry i should have specified that.

thanks
Dave
0
 
PortletPaulCommented:
This might work for you. It starts by building-up a 'range' of each minute in 24 hours, then joins that range to your call data to perform an aggregation which in effect is a count of all busy ports in each minute. Note that the call start point + duration is used to calculate an end point - and then every hour/minute that falls between the call start/end is matched.

There are 2 places in the code below where you need to update dates used:
a. inside the range
b. inside the call table subquery

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
-- note a.
             , to_date('15-04-2013','dd-mm-yyyy') + hr/24 + min/(24*60) pstart
             from hrs
             cross join mins
           )
select
   range.hrmin
 , sum(1) busy_ports
from range
                    /* this could be changed to left join */
inner join (
            select
              CALL_DATE_TIME_K as starts
            , CALL_DATE_TIME_K + INBOUND_DURATION/86400 as ends
            , INBOUND_DURATION as du
            from calls
-- note b.
            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
order by
     range.hrmin

Open in new window

Right now this is using an inner join which will then only list the hrs/mins where a call is being made. If you wanted to plot the whole 1440 minutes make that a left join.

if you don't want to work at minute precision it's really just a matter of tweaking how the range is built.

see this working at: http://sqlfiddle.com/#!4/7a273/1
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
WCGS_OperationsAuthor Commented:
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
0
 
WCGS_OperationsAuthor Commented:
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
0
 
WCGS_OperationsAuthor Commented:
Great help, Thanks
0
 
WCGS_OperationsAuthor Commented:
How would i output just the line for the highest busyport?

Dave
0
 
PortletPaulCommented:
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

Open in new window

see http://sqlfiddle.com/#!4/7a273/18
0
 
PortletPaulCommented:
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      

Open in new window

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

range now has 1440 records, min '0000', max '2359' (as it should be)
0
 
WCGS_OperationsAuthor Commented:
Paul,

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

Thanks
Dave
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now