Solved

oracle statement to calculate concurrent phone call peak in db

Posted on 2013-06-06
10
718 Views
Last Modified: 2013-06-07
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
Comment
Question by:WCGS_Operations
  • 6
  • 4
10 Comments
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39225817
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
 

Author Comment

by:WCGS_Operations
ID: 39225824
Yes each record is a port, and the duration is in seconds, sorry i should have specified that.

thanks
Dave
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 39226154
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
 

Author Comment

by:WCGS_Operations
ID: 39226242
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
 

Author Comment

by:WCGS_Operations
ID: 39226565
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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 

Author Closing Comment

by:WCGS_Operations
ID: 39226566
Great help, Thanks
0
 

Author Comment

by:WCGS_Operations
ID: 39226707
How would i output just the line for the highest busyport?

Dave
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39227731
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
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39228136
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
 

Author Comment

by:WCGS_Operations
ID: 39229062
Paul,

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

Thanks
Dave
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
I. Introduction There's an interesting discussion going on now in an Experts Exchange Group — Attachments with no extension (http://www.experts-exchange.com/discussions/210281/Attachments-with-no-extension.html). This reminded me of questions tha…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now