Solved

Call Utilization/Number of calls per hour - SQL Query

Posted on 2013-06-24
37
966 Views
Last Modified: 2016-02-26
Hi

Say,

We have a call detail records for telephone calls that are made. We wish to extract a report of the amount/number of simultaneous calls made over an hourly period.

The call detail record should contain the time, date, start, end & lastly duration. Kindly assist with a construction of a query to extract this data for 24hrs. & also for a 1 month period.

TX
0
Comment
Question by:shaunwingin
  • 23
  • 10
  • 2
37 Comments
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39271205
It normally works the other way around...
You tell us what the existing tablename and fieldnames are.
Sample data and expected results will also provide you with a better answer.

Is this a study exercise?
0
 

Author Comment

by:shaunwingin
ID: 39271677
Point taken. Please see attached.
Call-Details-April13-EE.xlsx
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39273947
Just noticed you have not specified the database vendor - this is quite essential as date arithmetic differ dramatically between them. I'll start assuming SQL Server (as there is a time column).

I'd like to clarify "simultaneous" if I may, does this exclude calls that have no overlap with any other call, "O" is part of the call duration that "overlaps"e.g.
zero 'simultaneous'
__________
          __________
                    __________
                              __________
                                        __________

2 simultaneous
__________OOO
          __________
                    __________
                              __________OOO
                                        __________

5 simultaneous                                        
__________OOO
          __________OOO
                    __________OOO
                              __________OOO
                                        __________OOO

Open in new window

0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39273992
here is a starting point, it is based on the assumption of SQL Server 2012. That version support a "lead()" function so the version is important. Oracle also supports this feature.

The further assumption is that "simultaneous" is derived from overlapping calls (as depicted above). If none of these assumptions regarding database, version or overlapping are true please advise.

a small sample of your data (part of 2013-04-04) has been used and is available here to inspect: http://sqlfiddle.com/#!6/8485c/7 where the query may be run for testing.
CALLDATE	HR	SIMULTANEOUS	NUM_CALLS
2013-04-04	6		1		1
2013-04-04	7		11		14
2013-04-04	8		8		10
2013-04-04	9		26		52
2013-04-04	10		15		24
2013-04-04	16		0		1

-- produced by this code:
SELECT
  convert(varchar(10),calldate,121) as calldate
, hr
, sum(overlap) AS simultaneous
, count(8) AS num_calls
FROM (
      SELECT
              calldate
            , callstart
            , datepart(hour,callstart) AS hr
            , dateadd(SECOND,duration_sec,callstart) AS callEnd
            , CASE WHEN lead(callstart) over (ORDER BY calldate, callstart)
                        > dateadd(SECOND,duration_sec,callstart)
                        THEN 1
                   ELSE 0
              END AS Overlap
      FROM calldata
      WHERE calldate >= '2013-04-04' -- for A date

      --where calldate >= '2013-04-01' -- for a MONTH, from this date
      --and   calldate  < '2013-05-01' -- and before this date

     ) AS derived
GROUP BY
  calldate
, hr

Open in new window

0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39274006
some added notes.

I chose some field names that differ to the spreadsheet headings as 'Date' and 'Time' are sql reserved words: CallDate, CallStart
similarly the headings with () within them were also renamed.

& below is a variant of your spreadsheet where you can see an analysis of the overlaps found in the call data.

nb: sqlfiddle only permits very small data samples - hence I could not replicate all your data there.

I would sincerely like to propose that you share the real tables and fieldnames in your dbms - those will make it easier for all concerned. Please do advise the dbms vendor and version (very important that version).

Cheers, Paul
Q-28165626-Call-Details-Inspect.xlsx
0
 

Author Comment

by:shaunwingin
ID: 39276118
Say, This is excellent feedback.
Its actually mysql (Microsoft SQL Server is just too complicated for me right now to work with). Please see attached data and table structure.
This data also includes the time of call with seconds.
There are really two queries, that are related.
1. The number of calls per hour calculated per minute. 1 call is also valid ie "simultaneous" is not strictly correct.
2. The number of calls per hour over a 24 hour period calculated over each hour.
1. is therefore a subset of 2.

The logic is that one is able to ultimately mine down from 2. into 1. for more detail.
Hope this is enough detail.
tTelkomDetailsMaster-Freq.sql
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39276708
excellent! thanks. now I know dbms type MySQL (and version, which is in a file, 5.0.95 ) and the real table structure. This makes quite a difference.

I'm a little worried about the validity of your data though, look at the dates stored:
The year is 2603 or 0000 - this is not so great
INSERT INTO `tTelkomDetailsMaster_Freq` (`InvoiceNo`, `Invoice number`, `Row description`, `Number called from`, `Final number called`, `InvNoOrig`, `Client`, `ClientGroup`,
  `Date of call`, `Time`, `Time of call`, `Duration of call`, `Description`, `Tariff`, `Cost of call`, `Call type`, `Source data`, `Surcharge/SCall`, `Number called to`, `auto`, `ID`, `uniqueID`) VALUES
... '2603-05-07', '14:06:54', 1, 2, NULL, 'SC', 0, 5, 2252, 0.038, '094471795', '598772', 8942041, 831),
... '2603-05-07', '14:42:29', 1, 117, NULL, 'SC', 0, 5, 2252, 2.233, '094471795', '598773', 8942042, 832),
... '0000-00-00', '09:11:19', 0, 59, NULL, 'SC', 0, 5, 2252, 1.126, '094471795', '598774', 8942043, 833),
... '0000-00-00', '09:12:50', 0, 1, NULL, 'SC', 0, 5, 2252, 0.609, '094471795', '598775', 8942044, 834),

Open in new window

can you confirm this is correct?
and. maybe you would also want to look at why those date are so weird
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39276918
there is more weird stuff in that table, e.g. field `Time` contains values like '60:47:20'

time cannot be greater than 23:59:59 (ignoring smaller units)

are you able to run this without error (I can't):

select
       `Date of call`
    ,  `Time`
    ,  `Time of call`
    ,  `Duration of call`
from tTelkomDetailsMaster_Freq

http://sqlfiddle.com/#!8/94caa/2
java.sql.SQLException: Illegal hour value '60' for java.sql.Time type in value '60:47:20.: select `Date of call` , `Time` , `Time of call` , `Duration of call` from tTelkomDetailsMaster_Freq

note this is being executed through Java - that may be the issue

what does a time like '60:47:20' represent?
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39276919
I can run this:

select
       `Date of call`
    ,  concat(`Time`,'') as x
    ,  `Time of call`
    ,  `Duration of call`
from tTelkomDetailsMaster_Freq
where left(concat(`Time`,''),1) in ('3','4','5','6','7','8','9')
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39276923
more...

what is `Time of call` (int)?
it can be zero but, in the same record,`Duration of call` >0

so is `Time of call` ignored?
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39276944
so, here is a simple query that will produce a count of calls per hour, but the data is a bot weird so results at your end may be surprising
SELECT
       date_format(`Date of call`,'%Y-%m-%d') AS `Date of call`
    ,  left(`Time`,2)                         AS HourString /* weird times ! */
    ,  count(*)                               AS NumOfCalls
    ,  avg(`Duration of call`)                AS avCallSecs
FROM tTelkomDetailsMaster_Freq

WHERE `Date of call` >= '2603-04-17' /* weird dates ! */
AND   `Date of call`  < '2603-04-26'

GROUP BY
       `Date of call`
    ,  left(`Time`,2) /* weird times ! */
ORDER BY
      `Date of call`
    , HourString

Open in new window

see this at: http://sqlfiddle.com/#!8/94caa/18
0
 

Author Comment

by:shaunwingin
ID: 39277143
Thank you for the detailed feedback. I must extend my apologies as I had used find and replace on the data to mask the tel no's and overlooked that the time and date would be affected.
Attached is corrected data
tTelkomDetailsMaster-Freq.sql
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39278233
I have created a summary of that data in sqlfiddle
http://sqlfiddle.com/#!8/3506c/1

the previous query still works - but the work is incomplete - & I have to delay doing more until (my) tomorrow
0
 

Author Comment

by:shaunwingin
ID: 39278253
Thanks, The result looks meaningful.  look forward to the morrow...
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39280365
Ok, if I have interpreted you intention, here are 2 queries that summarise your call data By Day/By Hour (range of days, with columns per hour) showing count of calls
In an Hour, drilling down from a particular cell  the next query shows no. of calls made in 5 minute blocks (as an example)

Is this sufficient for you?
DATE OF CALL	HR_0	HR_1	HR_2	HR_3	HR_4	HR_5	HR_6	HR_7	HR_8	HR_9	HR_10	HR_11	HR_12	HR_13	HR_14	HR_15	HR_16	HR_17	HR_18	HR_19	HR_20	HR_21	HR_22	HR_23	CALLS_PER_DAY
2013-05-02	0	0	0	0	0	0	0	0	2	5	2	4	3	3	2	2	4	0	0	0	0	0	0	0	27
2013-05-03	0	0	0	0	0	0	0	0	1	3	3	2	2	5	3	1	2	0	0	0	0	0	0	0	22
2013-05-06	0	0	0	0	0	0	0	0	2	3	4	3	0	4	5	2	0	0	0	0	0	0	0	0	23
2013-05-07	0	0	0	0	0	0	0	0	2	0	2	2	1	3	2	5	3	0	0	0	0	0	0	0	20
2013-05-08	0	0	0	0	0	0	1	1	2	1	5	0	2	2	3	3	5	2	0	0	0	0	0	0	27
2013-05-09	0	0	0	0	0	0	0	0	1	0	4	4	3	5	2	3	3	1	0	0	0	0	0	0	26

Open in new window

drill down from a cell above, to detail below (e.g. 2013-05-02, hour 9)
DATE OF CALL	HOUR_OF_CALL	MI_0	MI_5	MI_10	MI_15	MI_20	MI_25	MI_30	MI_35	MI_40	MI_45	MI_50	MI_55	CALLS_IN_HOUR
2013-05-02	9		0	0	0	0	0	2	0	1	1	0	0	1	5

Open in new window

the SQL code for these:
SELECT
      date_format(`Date of call`,'%Y-%m-%d') AS `Date of call`
    , count( case when hour(`Time`) = 0  then `Duration of call` end ) as hr_0
    , count( case when hour(`Time`) = 1  then `Duration of call` end ) as hr_1
    , count( case when hour(`Time`) = 2  then `Duration of call` end ) as hr_2
    , count( case when hour(`Time`) = 3  then `Duration of call` end ) as hr_3
    , count( case when hour(`Time`) = 4  then `Duration of call` end ) as hr_4
    , count( case when hour(`Time`) = 5  then `Duration of call` end ) as hr_5
    , count( case when hour(`Time`) = 6  then `Duration of call` end ) as hr_6
    , count( case when hour(`Time`) = 7  then `Duration of call` end ) as hr_7
    , count( case when hour(`Time`) = 8  then `Duration of call` end ) as hr_8
    , count( case when hour(`Time`) = 9  then `Duration of call` end ) as hr_9
    , count( case when hour(`Time`) = 10 then `Duration of call` end ) as hr_10
    , count( case when hour(`Time`) = 11 then `Duration of call` end ) as hr_11
    , count( case when hour(`Time`) = 12 then `Duration of call` end ) as hr_12
    , count( case when hour(`Time`) = 13 then `Duration of call` end ) as hr_13
    , count( case when hour(`Time`) = 14 then `Duration of call` end ) as hr_14
    , count( case when hour(`Time`) = 15 then `Duration of call` end ) as hr_15
    , count( case when hour(`Time`) = 16 then `Duration of call` end ) as hr_16
    , count( case when hour(`Time`) = 17 then `Duration of call` end ) as hr_17
    , count( case when hour(`Time`) = 18 then `Duration of call` end ) as hr_18
    , count( case when hour(`Time`) = 19 then `Duration of call` end ) as hr_19
    , count( case when hour(`Time`) = 20 then `Duration of call` end ) as hr_20
    , count( case when hour(`Time`) = 21 then `Duration of call` end ) as hr_21
    , count( case when hour(`Time`) = 22 then `Duration of call` end ) as hr_22
    , count( case when hour(`Time`) = 23 then `Duration of call` end ) as hr_23
    , count(*) as calls_per_day
FROM tTelkomDetailsMaster_Freq

WHERE `Date of call` >= '2013-01-01'
AND   `Date of call`  < '2014-01-01'

GROUP BY
       `Date of call`
ORDER BY
      `Date of call`
;

Open in new window

set @day = '2013-05-02';
set @hour = 9;

SELECT
      date_format(`Date of call`,'%Y-%m-%d') AS `Date of call`
    , @hour as hour_of_call
    , count( case when minute(`Time`) >= 0  and minute(`Time`) < 5  then `Duration of call` end ) as mi_0
    , count( case when minute(`Time`) >= 5  and minute(`Time`) < 10 then `Duration of call` end ) as mi_5
    , count( case when minute(`Time`) >= 10 and minute(`Time`) < 15 then `Duration of call` end ) as mi_10
    , count( case when minute(`Time`) >= 15 and minute(`Time`) < 20 then `Duration of call` end ) as mi_15
    , count( case when minute(`Time`) >= 20 and minute(`Time`) < 25 then `Duration of call` end ) as mi_20
    , count( case when minute(`Time`) >= 25 and minute(`Time`) < 30 then `Duration of call` end ) as mi_25
    , count( case when minute(`Time`) >= 30 and minute(`Time`) < 35 then `Duration of call` end ) as mi_30
    , count( case when minute(`Time`) >= 35 and minute(`Time`) < 40 then `Duration of call` end ) as mi_35
    , count( case when minute(`Time`) >= 40 and minute(`Time`) < 45 then `Duration of call` end ) as mi_40
    , count( case when minute(`Time`) >= 45 and minute(`Time`) < 50 then `Duration of call` end ) as mi_45
    , count( case when minute(`Time`) >= 50 and minute(`Time`) < 55 then `Duration of call` end ) as mi_50
    , count( case when minute(`Time`) >= 55 and minute(`Time`) < 60 then `Duration of call` end ) as mi_55
    , count(*) as calls_in_hour
FROM tTelkomDetailsMaster_Freq

WHERE `Date of call` = @day
AND hour(`Time`) = @hour
GROUP BY
       `Date of call`
;

Open in new window

demonstration: http://sqlfiddle.com/#!2/3506c/14
0
 

Author Comment

by:shaunwingin
ID: 39280761
This is looking very on track.
I have a question as to the logic behind this case statement:
count( case when minute(`Time`) >= 0  and minute(`Time`) < 5  then `Duration of call` end ) as mi_0

What does " then `Duration of call`" mean and result in?

To explain further, a all made within the same hour may end up without an overlapping duration if the duration of the two is not compared.

We are after knowing if there are too few lines i.e. if all the lines are busy at a certain time....

If you have taken this into account but I can't understand how.
Tx Shaun
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39280800
the count() function requires a non-null value, any non-null value, so the choice of field was quite arbitrary.

i.e. count() will add 1 if there is a value, but ignore any nulls.

I could have used:

count( case when minute(`Time`) >= 0  and minute(`Time`) < 5  then 1 end ) as mi_0
or
count( case when minute(`Time`) >= 0  and minute(`Time`) < 5  then 'x' end ) as mi_0

by the way, these queries are really based on the 'start of a call', the duration does not matter. If you look at the average durations any influence of duration is (sorry) trivial in the overall scheme and certainly not very relevant at hourly increments.

In any case, you did rule out special consideration of "simultaneous" quite some time ago.
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:shaunwingin
ID: 39280859
Thanks for the explanation.
There's been a misunderstanding as I said:
"1. The number of calls per hour calculated per minute. 1 call is also valid ie "simultaneous" is not strictly correct."

The point is "also". You query is good if there is only one call, but we need to know how many simultaneous calls as well I'm afraid.
Any ideas?

PS thank you for the excellent way you have displayed the results.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39280897
"also" makes no difference I'm afraid, note the "5 calls" below
zero 'simultaneous', 5 calls
__________
          __________
                    __________
                              __________
                                        __________

2 simultaneous, 5 calls
__________OOO
          __________
                    __________
                              __________OOO
                                        __________

5 simultaneous , 5 calls
__________OOO
          __________OOO
                    __________OOO
                              __________OOO
                                        __________OOO

Open in new window

there is a possibility that a calls spans one of the boundaries, and one could argue then that one call be counted in the starting "bucket" as well as the finishing "bucket" (but 1 call now becomes 2 calls, or you start splitting calls to 0.5) - which my queries do not do. One call is one call only, and it is counted at the point of commencement.

a possible refinement could be to count a call at the midpoint of the call duration
but please do look closely at the call durations, such a refinement is not likely to add to the value of the data IMHO.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39280950
If you really need count of all concurrent calls in any time period, then this is way more complex

- and I don't know how to achieve this in MySQL (without research at least)
- in Oracle or SQL Server I do know how

in the spreadsheet you provided (1946 call records) the modal duration is 70 seconds
(average 156.7), but there are some calls that would span an hour.

basically to achieve a much more refined method requires building a table of ate/time boundaries and then counting any call durations starting/finishing or spanning each.
0
 

Author Comment

by:shaunwingin
ID: 39281227
Thank you for the feedback and honesty.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39281263
I have found possible methods in MySQL for the missing parts.

In Oracle/SQL Server I would use "recursive queries" to build a table of date boundaries, then join the data to that, and then evaluate the data against the boundaries. Recursive queries don't exist in MySQL (as of now anyway) - so to build the missing parts requires other techniques.

This page identifies some approaches to overcome this, and the last listed stored procedure appears (with a tweak or 2) to be relevant.
http://www.artfulsoftware.com/infotree/qrytip.php?id=95

there's quite a  bit here I'm not that familiar with and I may not have time for it.

How strong are you with MySQL? is that stored proc something you can handle?

what is missing from it (for your purpose is a "second date", i.e. each record should have a start and an end, that proc only gives the start)
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39281366
here are the tweaks needed on that sp... I have changed some names also:
CREATE PROCEDURE dt_buckets_sp( pstart datetime, pstop datetime, pminutes int ) 
DETERMINISTIC 
BEGIN 
  DECLARE thisdate datetime; 
  DECLARE thatdate datetime; 
  DROP TABLE IF EXISTS dt_buckets; 
  CREATE TABLE dt_buckets( start_at datetime , stop_at datetime);  
  SET thisdate=pstart; 
  SET thatdate = adddate( thisdate, INTERVAL pminutes MINUTE ); 
  INSERT INTO dt_buckets VALUES(pstart, thatdate); 
  WHILE thatdate < pstop DO 
    SET thisdate = adddate( thisdate, INTERVAL pminutes MINUTE ); 
    SET thatdate = adddate( thisdate, INTERVAL pminutes MINUTE ); 
    INSERT INTO dt_buckets VALUES( thisdate, thatdate ); 
  END WHILE; 
END 
/

Open in new window

usage:

CALL dt_buckets_sp('2013-05-02 00:00:00', '2013-05-10 00:00:00', 60);

select * from dt_buckets
;

see: http://sqlfiddle.com/#!2/c2927a/1
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39281524
and here is another step in the adventure (something of a bargain it seems)...

http://sqlfiddle.com/#!2/1d7442/1
CALL dt_buckets_sp('2013-05-02 00:00:00', '2013-05-10 00:00:00', 60); 

select
  start_at
, start
, stop
, stop_at
, `Duration of call`
, auto
from dt_buckets as B
left join (
          select
            `auto`
          , `Duration of call`
          , addtime(`Date of call`, `Time`) as start
          , addtime( addtime(`Date of call`, `Time`), SEC_TO_TIME(`Duration of call`)) as stop
          from tTelkomDetailsMaster_Freq
          ) as c on (    c.start >= B.start_at and c.start < B.stop_at
                      or c.stop  >= B.start_at and c.stop  < B.stop_at
                      or B.start_at >= c.start and B.stop_at < c.stop
                    )
where `Duration of call` > 500
order by auto, start_at
;

Open in new window

what this is doing is spreading the call duration across a number of datetime buckets (e.g. if a call lasts 3 hours you will get a total count of 3, 1 per hour)

from here, we proceed back to the 'pivot' over 24 hours as in the earlier display...

when will this end?
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 39281596
Please look to the far right, there you will see the impact of this extra effort (for which I get no extra points :(
DATE OF CALL	HR_0	HR_1	HR_2	HR_3	HR_4	HR_5	HR_6	HR_7	HR_8	HR_9	HR_10	HR_11	HR_12	HR_13	HR_14	HR_15	HR_16	HR_17	HR_18	HR_19	HR_20	HR_21	HR_22	HR_23	CONCURRENT_CPD	ACTUAL_CPD
2013-05-02	0	0	0	0	0	0	0	0	2	5	2	6	5	4	2	2	4	0	0	0	0	0	0	0	32		27
2013-05-03	0	0	0	0	0	0	0	0	1	3	3	2	2	5	3	3	3	0	0	0	0	0	0	0	25		22
2013-05-04	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0		0
2013-05-05	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0		0
2013-05-06	0	0	0	0	0	0	0	0	2	3	4	3	0	4	5	2	0	0	0	0	0	0	0	0	23		23
2013-05-07	0	0	0	0	0	0	0	0	2	0	2	2	1	4	2	5	3	0	0	0	0	0	0	0	21		20
2013-05-08	0	0	0	0	0	0	1	2	2	1	5	0	2	2	3	4	5	2	1	0	0	0	0	0	30		27
2013-05-09	0	0	0	0	0	0	0	0	1	0	4	4	5	5	2	2	2	1	0	0	0	0	0	0	26		24

Open in new window

here is the code to produce that grid:
CALL dt_buckets_sp('2013-05-02 00:00:00', '2013-05-10 00:00:00', 60);

SELECT
      date_format(start_at,'%Y-%m-%d') AS `Date of call`
    , count( CASE WHEN hour(start_at) = 0  THEN `auto` END ) AS hr_0
    , count( CASE WHEN hour(start_at) = 1  THEN `auto` END ) AS hr_1
    , count( CASE WHEN hour(start_at) = 2  THEN `auto` END ) AS hr_2
    , count( CASE WHEN hour(start_at) = 3  THEN `auto` END ) AS hr_3
    , count( CASE WHEN hour(start_at) = 4  THEN `auto` END ) AS hr_4
    , count( CASE WHEN hour(start_at) = 5  THEN `auto` END ) AS hr_5
    , count( CASE WHEN hour(start_at) = 6  THEN `auto` END ) AS hr_6
    , count( CASE WHEN hour(start_at) = 7  THEN `auto` END ) AS hr_7
    , count( CASE WHEN hour(start_at) = 8  THEN `auto` END ) AS hr_8
    , count( CASE WHEN hour(start_at) = 9  THEN `auto` END ) AS hr_9
    , count( CASE WHEN hour(start_at) = 10 THEN `auto` END ) AS hr_10
    , count( CASE WHEN hour(start_at) = 11 THEN `auto` END ) AS hr_11
    , count( CASE WHEN hour(start_at) = 12 THEN `auto` END ) AS hr_12
    , count( CASE WHEN hour(start_at) = 13 THEN `auto` END ) AS hr_13
    , count( CASE WHEN hour(start_at) = 14 THEN `auto` END ) AS hr_14
    , count( CASE WHEN hour(start_at) = 15 THEN `auto` END ) AS hr_15
    , count( CASE WHEN hour(start_at) = 16 THEN `auto` END ) AS hr_16
    , count( CASE WHEN hour(start_at) = 17 THEN `auto` END ) AS hr_17
    , count( CASE WHEN hour(start_at) = 18 THEN `auto` END ) AS hr_18
    , count( CASE WHEN hour(start_at) = 19 THEN `auto` END ) AS hr_19
    , count( CASE WHEN hour(start_at) = 20 THEN `auto` END ) AS hr_20
    , count( CASE WHEN hour(start_at) = 21 THEN `auto` END ) AS hr_21
    , count( CASE WHEN hour(start_at) = 22 THEN `auto` END ) AS hr_22
    , count( CASE WHEN hour(start_at) = 23 THEN `auto` END ) AS hr_23
    , count(`auto`)          AS concurrent_cpd
    , count(DISTINCT `auto`) AS actual_cpd
FROM (
        SELECT
          start_at
        , START
        , stop
        , stop_at
        , `Duration of call`
        , auto
        FROM dt_buckets AS B
        LEFT JOIN (
                  SELECT
                    `auto`
                  , `Duration of call`
                  , addtime(`Date of call`, `Time`) AS START
                  , addtime( addtime(`Date of call`, `Time`), SEC_TO_TIME(`Duration of call`)) AS stop
                  FROM tTelkomDetailsMaster_Freq
                  ) AS c ON (    c.START >= B.start_at AND c.START < B.stop_at
                              OR c.stop  >= B.start_at AND c.stop  < B.stop_at
                              OR B.start_at >= c.START AND B.stop_at < c.stop
                            )
     ) AS derived
GROUP BY
     date_format(start_at,'%Y-%m-%d')
ORDER BY
     `Date of call`
;

Open in new window

http://sqlfiddle.com/#!2/1d7442/4
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39281705
can this question be closed off yet?
0
 

Author Comment

by:shaunwingin
ID: 39282499
Thank you very much. The comparison is appreciated between your 1st and now this implementation. The more significant result is 6 concurrent calls appear at some points with this as opposed to only 5 with estimate. This data was made from a system with a maximum of 8 channels ie. 4xISDN lines and either they never reach peak or perhaps one ISDN ( 2channels per ISDN) is faulty.

I'm going to close question although I've a favor ask...
0
 

Author Closing Comment

by:shaunwingin
ID: 39282508
Thank you for your fine answer and excellent presentation!

If you can perhaps assist me with the coding direction I should take to achieve the 5 minute drill down I would appreciate as I'm not familiar with the "advanced" MySql you have mastered. tx again.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39283251
Please note a couple of things...

I was not able to load the full data set you provided at sqlfiddle (it limits DDL to a total of 8000 characters)
I did make 2 amendments in that data to test 3 hour long calls (actually 1 second less than 3 hours and 1 second longer than 3 hours), this is why you see a result of 6 I believe, but also consider the point above.
I have used a stored procedure, and it occurs to me now that only recent versions of MySQL support these. Please check if your version does allow stored procedures.

manually changed:
('2013-05-02 00:00:00', '10:00:00', 0, 10799, 089),
('2013-05-02 00:00:00', '10:00:00', 0, 10801, 090),
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39283840
for the record, here is the 'drill down' ( using 2013-05-02 at 09:00 )

set @day = '2013-05-02';
set @hour = 9;

CALL dt_buckets_sp( adddate(@day, INTERVAL @hour HOUR) , adddate(@day, INTERVAL (@hour+1) HOUR), 5);
                     
SELECT
      date_format(start_at,'%Y-%m-%d') AS `Date of call`
    , @hour as hour_of_call
    , count( case when minute(start_at) >= 0  and minute(start_at) < 5  then `auto` end ) as mi_0
    , count( case when minute(start_at) >= 5  and minute(start_at) < 10 then `auto` end ) as mi_5
    , count( case when minute(start_at) >= 10 and minute(start_at) < 15 then `auto` end ) as mi_10
    , count( case when minute(start_at) >= 15 and minute(start_at) < 20 then `auto` end ) as mi_15
    , count( case when minute(start_at) >= 20 and minute(start_at) < 25 then `auto` end ) as mi_20
    , count( case when minute(start_at) >= 25 and minute(start_at) < 30 then `auto` end ) as mi_25
    , count( case when minute(start_at) >= 30 and minute(start_at) < 35 then `auto` end ) as mi_30
    , count( case when minute(start_at) >= 35 and minute(start_at) < 40 then `auto` end ) as mi_35
    , count( case when minute(start_at) >= 40 and minute(start_at) < 45 then `auto` end ) as mi_40
    , count( case when minute(start_at) >= 45 and minute(start_at) < 50 then `auto` end ) as mi_45
    , count( case when minute(start_at) >= 50 and minute(start_at) < 55 then `auto` end ) as mi_50
    , count( case when minute(start_at) >= 55 and minute(start_at) < 60 then `auto` end ) as mi_55
    , count(`auto`)          AS concurrent_cpd
    , count(DISTINCT `auto`) AS actual_cpd
FROM (
        SELECT
          start_at
        , START
        , stop
        , stop_at
        , `Duration of call`
        , auto
        FROM dt_buckets AS B
        LEFT JOIN (
                  SELECT
                    `auto`
                  , `Duration of call`
                  , addtime(`Date of call`, `Time`) AS START
                  , addtime( addtime(`Date of call`, `Time`), SEC_TO_TIME(`Duration of call`)) AS stop
                  FROM tTelkomDetailsMaster_Freq
                  ) AS c ON (    c.START >= B.start_at AND c.START < B.stop_at
                              OR c.stop  >= B.start_at AND c.stop  < B.stop_at
                              OR B.start_at >= c.START AND B.stop_at < c.stop
                            )
     ) AS derived
GROUP BY
     date_format(start_at,'%Y-%m-%d')
;

Open in new window

this can be seen at: http://sqlfiddle.com/#!2/a6396/19 together with the multi-day query
1
 

Author Comment

by:shaunwingin
ID: 39287647
Thank you very much PortletPaul!
Please can you help me understand what these are and why the difference:
CONCURRENT_CPD (8)      ACTUAL_CPD (5)

Does it pertain to this comment of yours?
ID: 39280897
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39287668
sort of
    , count(`auto`)          AS concurrent_cpd
    , count(DISTINCT `auto`) AS actual_cpd -- nb the "DISTINCT"

cpd = calls per day

actual_cpd
are literally how many times the a handset was used (i.e. the number of source records in your table)
concurrent_cpd
a count of time units spanned by all calls, hence long calls can be greater than 1

may I make this small point?

you have got good value from this one question I believe
we are volunteers, our reward is points
I did see you additional request
does this mean you cannot use stored procedures?
however I agree with the admins
ask another question please, reference this question
specify you cannot use stored procedures
& specify your MySQL version too
of course an upgrade of MySQL would allow you to use stored procedures.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39287848
http://sqlfiddle.com/#!2/dc220/2

create table ints(i tinyint); 
insert into ints values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9); 



set @S := '2010-10-01';
set @E := '2010-10-10';

SELECT
    @S + INTERVAL t.i*100 + u.i*10 + v.i HOUR                     AS Start_at 
, ( @S + INTERVAL t.i*100 + u.i*10 + v.i HOUR ) + INTERVAL 1 HOUR AS Stop_at
FROM ints AS t  
JOIN ints AS u 
JOIN ints AS v 
WHERE ( @S + INTERVAL t.i*100 + u.i*10 + v.i HOUR )  < @E
ORDER BY start_at DESC
; 



set @S := '2010-10-09';
set @E := '2010-10-10';

SELECT
    @S + INTERVAL (t.i*100 + u.i*10 + v.i)*5 MINUTE                       AS Start_at 
, ( @S + INTERVAL (t.i*100 + u.i*10 + v.i)*5 MINUTE ) + INTERVAL 5 MINUTE AS Stop_at
FROM ints AS t  
JOIN ints AS u 
JOIN ints AS v 
WHERE ( @S + INTERVAL (t.i*100 + u.i*10 + v.i)*5 MINUTE )  < @E
ORDER BY start_at DESC
; 

Open in new window

reading:
http://sqlfiddle.com/#!2/dc220/2
0
 

Expert Comment

by:Rami Hachem
ID: 41481676
Hello Paul Maxwell
this is awesome stuff!!! thank YOU!
I just would like some help with one of your comments

i see that you have a query ( http://sqlfiddle.com/#!2/a6396/19) to report on the number of calls every 5 minutes,  i believe you are hardcoding the hour that you are looking for, the value is 9.

is there a way instead of hardcoding the hour, to display for every hour based on date?

so if i chose, give the number of calls for every 5 minutes, i will get 12 rows, each row represent an hour of the day? and every column in that row will represent a 5 minute interval?


Date                H    0       5     10    15   20    25    30   35    40    45   50   55
2013-05-02      0     0      0      0      0      0      2      2      1      1      1      0      1
2013-05-02      1     0      0      0      0      0      2      2      1      1      1      0      1
2013-05-02      2     0      0      0      0      0      2      2      1      1      1      0      1
2013-05-02      3     0      0      0      0      0      2      2      1      1      1      0      1
2013-05-02      4    0      0      0      0      0      2      2      1      1      1      0      1
2013-05-02      5     0      0      0      0      0      2      2      1      1      1      0      1
2013-05-02      6     0      0      0      0      0      2      2      1      1      1      0      1
2013-05-02      7     0      0      0      0      0      2      2      1      1      1      0      1
2013-05-02      8     0      0      0      0      0      2      2      1      1      1      0      1
2013-05-02      9     0      0      0      0      0      2      2      1      1      1      0      1
2013-05-02      10   0      0      0      0      0      2      2      1      1      1      0      1
2013-05-02      11   0      0      0      0      0      2      2      1      1      1      0      1
2013-05-02      12   0      0      0      0      0      2      2      1      1      1      0      1
2013-05-02      1     0      0      0      0      0      2      2      1      1      1      0      1
2013-05-02      2     0      0      0      0      0      2      2      1      1      1      0      1
2013-05-02      3     0      0      0      0      0      2      2      1      1      1      0      1
2013-05-02      4    0      0      0      0      0      2      2      1      1      1      0      1
2013-05-02      5     0      0      0      0      0      2      2      1      1      1      0      1
2013-05-02      6     0      0      0      0      0      2      2      1      1      1      0      1
2013-05-02      7     0      0      0      0      0      2      2      1      1      1      0      1
2013-05-02      8     0      0      0      0      0      2      2      1      1      1      0      1
2013-05-02      9     0      0      0      0      0      2      2      1      1      1      0      1
2013-05-02      10   0      0      0      0      0      2      2      1      1      1      0      1
2013-05-02      11   0      0      0      0      0      2      2      1      1      1      0      1


Thank you,
0
 

Expert Comment

by:Rami Hachem
ID: 41482282
Paul,

I'm sorry about that. that was my first port on this site and will open a new question.

Rami
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Are you one of those front-line IT Service Desk staff fielding calls, replying to emails, all-the-while working to resolve end-user technological nightmares? I am! That's why I have put together this brief overview of tools and techniques I use in o…
Live is the evolution of Q&A. Get your technology problems solved instantly by connecting with technology experts instantly. Pair programming has never been easier.
This video walks the viewer through the process of creating Hyperlinks for the web and other documents. Select the "Insert" tab: Click "Hyperlink":  Type "http://" followed by a web address to reference a website or navigate to a document to ref…
An overview on how to enroll an hourly employee into the employee database and how to give them access into the clock in terminal.

746 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

11 Experts available now in Live!

Get 1:1 Help Now