Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

oracle statement to calculate concurrent phone call peak in db

Posted on 2013-06-06
10
Medium Priority
?
1,049 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 49

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 49

Accepted Solution

by:
PortletPaul earned 2000 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
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 

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
 

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 49

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 49

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

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Suggested Courses

971 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