Hi I need help working out how to calculate how many peak concurrent calls I have in a day from my CDR date stored in MySQL.
The data set looks like this:
INSERT INTO `cdr` (`calldate`, `clid`, `src`, `dst`, `dcontext`, `channel`, `dstchannel`,
`lastapp`, `lastdata`, `duration`, `billsec`, `disposition`, `amaflags`,
`accountcode`, `uniqueid`, `userfield`) VALUES
I can user the following query to calculate how many entries there are for each unique date.
SELECT COUNT(1) AS entries, date(calldate) AS DATE
FROM `cdr`
GROUP BY DATE (calldate)
LIMIT 0 , 1000
However this only tells me the theoretical peak of concurrent calls are and not the actual peak.
To get the actual peak we need to first know the start and finish dates and times of each call. Currently the start date and time is recorded in the (calldate) field and the duration of the call is recorded in the (duration) field in seconds. By adding the seconds stored in the (duration) field to the (calldate) field we are able to calculate the finish time.
Now that we know the start and finish times we need to calculate if these times overlap and by how many times. This level of SQL query is well beyond my knowledge.
To recap I am trying to calculate with a MySQL query what the peak number of simultaneous calls are from the CDR data stored in MySQL. Any help is gratefully received
Sample Data:
calldate,clid,src,dst,dcontext,channel,dstchannel,lastapp,lastdata,duration,billsec,disposition,amaflags,accountcode,uniqueid,userfield
08/11/2013 17:02,x,x,1000,default,x,x,x,x,26,26,ANSWERED,3,x,1383930162,x
08/11/2013 17:02,x,x,1000,default,x,x,x,x,24,24,ANSWERED,3,x,1383930164,x
08/11/2013 17:02,x,x,1000,default,x,x,x,x,45,45,ANSWERED,3,x,1383930146,x
08/11/2013 17:10,x,x,1000,default,x,x,x,x,2,2,ANSWERED,3,x,1383930649,x
08/11/2013 17:22,x,x,1000,default,x,x,x,x,4,4,ANSWERED,3,x,1383931380,x
08/11/2013 17:23,x,x,1000,default,x,x,x,x,5,5,ANSWERED,3,x,1383931388,x
08/11/2013 17:23,x,x,1000,default,x,x,x,x,9,9,ANSWERED,3,x,1383931395,x
10/11/2013 09:28,x,x,1000,default,x,x,x,x,7,7,ANSWERED,3,x,1384075689,x
10/11/2013 10:09,x,x,1000,default,x,x,x,x,57,57,ANSWERED,3,x,1384078181,x
10/11/2013 10:09,x,x,1000,default,x,x,x,x,81,81,ANSWERED,3,x,1384078164,x
10/11/2013 10:09,x,x,1000,default,x,x,x,x,102,102,ANSWERED,3,x,1384078143,x
11/11/2013 10:23,x,x,1000,default,x,x,x,x,3,3,ANSWERED,3,x,1384165439,x
11/11/2013 17:46,x,x,1000,default,x,x,x,x,30,30,ANSWERED,3,x,1384191975,x
11/11/2013 17:46,x,x,1000,default,x,x,x,x,30,30,ANSWERED,3,x,1384191976,x
11/11/2013 17:45,x,x,1000,default,x,x,x,x,50,50,ANSWERED,3,x,1384191956,x
11/11/2013 17:55,x,x,1000,default,x,x,x,x,9,9,ANSWERED,3,x,1384192544,x
13/11/2013 10:59,x,x,1000,default,x,x,x,x,209,209,ANSWERED,3,x,1384340382,x
13/11/2013 10:59,x,x,1000,default,x,x,x,x,230,230,ANSWERED,3,x,1384340361,x
13/11/2013 11:09,x,x,1000,default,x,x,x,x,1342,1342,ANSWERED,3,x,1384340963,x
13/11/2013 11:10,x,x,1000,default,x,x,x,x,1312,1312,ANSWERED,3,x,1384341009,x
13/11/2013 11:08,x,x,1000,default,x,x,x,x,1441,1441,ANSWERED,3,x,1384340891,x
13/11/2013 11:10,x,x,1000,default,x,x,x,x,1288,1288,ANSWERED,3,x,1384341059,x
13/11/2013 11:10,x,x,1000,default,x,x,x,x,1306,1306,ANSWERED,3,x,1384341050,x
13/11/2013 11:09,x,x,1000,default,x,x,x,x,1378,1378,ANSWERED,3,x,1384340990,x
13/11/2013 11:09,x,x,1000,default,x,x,x,x,1419,1419,ANSWERED,3,x,1384340953,x
13/11/2013 11:06,x,x,1000,default,x,x,x,x,1558,1558,ANSWERED,3,x,1384340815,x
13/11/2013 11:12,x,x,1000,default,x,x,x,x,1254,1254,ANSWERED,3,x,1384341121,x
13/11/2013 11:10,x,x,1000,default,x,x,x,x,1330,1330,ANSWERED,3,x,1384341045,x
13/11/2013 11:09,x,x,1000,default,x,x,x,x,1431,1431,ANSWERED,3,x,1384340947,x
13/11/2013 11:11,x,x,1000,default,x,x,x,x,1302,1302,ANSWERED,3,x,1384341076,x
13/11/2013 11:09,x,x,1000,default,x,x,x,x,1383,1383,ANSWERED,3,x,1384340995,x
13/11/2013 11:08,x,x,1000,default,x,x,x,x,1444,1444,ANSWERED,3,x,1384340937,x
13/11/2013 11:07,x,x,1000,default,x,x,x,x,1531,1531,ANSWERED,3,x,1384340850,x
13/11/2013 11:09,x,x,1000,default,x,x,x,x,1418,1418,ANSWERED,3,x,1384340963,x
13/11/2013 12:02,x,x,1000,default,x,x,x,x,10,10,ANSWERED,3,x,1384344169,x
13/11/2013 12:01,x,x,1000,default,x,x,x,x,807,807,ANSWERED,3,x,1384344072,x
13/11/2013 12:03,x,x,1000,default,x,x,x,x,680,680,ANSWERED,3,x,1384344200,x
13/11/2013 12:01,x,x,1000,default,x,x,x,x,793,793,ANSWERED,3,x,1384344090,x
13/11/2013 12:01,x,x,1000,default,x,x,x,x,772,772,ANSWERED,3,x,1384344111,x
This one should work, but is a real performance killer!
SELECT
calldate,
MAX(concurrent)+1 AS peakcount
FROM (
SELECT
DATE(a.calldate) as calldate,
COUNT(b.uniqueid) AS concurrent
FROM cdr AS a, cdr AS b
WHERE
a.calldate BETWEEN '2013-11-08 00:00:00' AND '2013-11-13 23:59:59'
AND (
(a.calldate<=b.calldate AND (UNIX_TIMESTAMP(a.calldate)+a.duration)>=UNIX_TIMESTAMP(b.calldate))
OR (b.calldate<=a.calldate AND (UNIX_TIMESTAMP(b.calldate)+b.duration)>=UNIX_TIMESTAMP(a.calldate))
)
AND a.uniqueid>b.uniqueid
GROUP BY a.uniqueid
) AS baseview
GROUP BY calldate
gives the correct answers for your example data. Here is, how it works:
a.calldate<=b.calldate AND (UNIX_TIMESTAMP(a.calldate)+a.duration)>=UNIX_TIMESTAMP(b.calldate)...) calculates the intersection: Two calls overlap, if the starting point of one call is at or after the starting point of the other call and at or before the ending point of that callAND a.uniqueid>b.uniqueid selector and GROUP BY a.uniqueid, which makes only the call with the smallest uniqueid see all concurrent calls, the others see lessMAX() on this in the outer query filters out this record+1 to get the peak call count: A call with 2 concurrent calls means a peak count of 3