4

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
user3246938
  • 89
  • 2
  • 12
  • It is always helpful if you provide sample data. What are the fields that have the start and end times of the call? – Gordon Linoff Jan 29 '14 at 02:11
  • The start time is (calldate) and the end date needs to be calculated by adding the duration which is stored in seconds in the field (duration) – user3246938 Jan 29 '14 at 12:25

3 Answers3

4

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:

  • The innermost part (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 call
  • Self-joining the call tables finds all overlaps,
  • but with a problem: The self join finds an over lap between lines 1 and 2, but another one with lines 2 and 1. If more than two calls overlap, it is tedious to sort this out
  • Now since your data contains a numeric unique ID, we can use this to filter those duplicates, triplicates etc. this is done by the AND 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 less
  • Using MAX() on this in the outer query filters out this record
  • We need the +1 to get the peak call count: A call with 2 concurrent calls means a peak count of 3

SQLfiddle

Eugen Rieck
  • 64,175
  • 10
  • 70
  • 92
  • Thanks for the script. I think I can reduce the performance issues by running it at the end of each day and writing the results to a table. This way I only need to run the query for one days worth of data at a time and can run further reports on the stored results. – user3246938 Jan 29 '14 at 11:18
  • I have tested the script and get the following error. #1305 - FUNCTION asteriskcdr.UNIX_TIMESTAM does not exist. Perhaps I need to reference the table "cdr" – user3246938 Jan 29 '14 at 11:20
  • Sorry I see the typo now I have posted a comment. This looks good. It produces a list of numbers consistent with my thoughts. however it the output doesn't say which day the peak occurred. It would be good to list a date then the peak for the date. Thanks again for this working script. I have learned something today. – user3246938 Jan 29 '14 at 11:38
  • I fixed the typo, sorry for that! I also edited the script to show date and peakcount as requested. – Eugen Rieck Jan 29 '14 at 11:44
  • Many thanks Eugen. The results are interesting. 2013-11-13 2 2013-11-13 2 2013-11-13 16 2013-11-13 16 2013-11-13 16 2013-11-13 16 2013-11-13 16 2013-11-13 16 2013-11-13 32 2013-11-13 16 2013-11-13 16 2013-11-13 16 2013-11-13 16 2013-11-13 5 2013-11-13 5 2013-11-13 5 2013-11-13 4 Looking at the output the peak is (2013-11-13 32) How do we show this a single peak result for the day and further more how do we write results to a seperate table? for example trunkdb.log calldate peakcount finally I will want to run this daily for the previous days data. Many thanks again for this fantastic help. – user3246938 Jan 29 '14 at 12:04
  • I've been trying to work the MAX command in but no matter where I put it I get errors. Is it possible to combine GROUP with MAX or COUNT with MAX – user3246938 Jan 30 '14 at 08:59
  • It was a simple omission in my SQL: Use `GROUP BY DATE(a.calldate)` instead of `GROUP BY a.calldate` (as is now in the fixed answer). Sorry for that! – Eugen Rieck Jan 30 '14 at 11:43
  • Thanks for the reply. Adding the brackets produces numbers which can't be correct. By picking the MAX peakcount for a given date is the correct result. I can probably work out how to how to run the report for date range and I suspect substitute the group for an insert to write to a separate table. How do we select only the max records for each day? – user3246938 Jan 30 '14 at 11:55
  • Thanks again for the update. There is an issue. The out put is multiplying the peakcount. The answer we are looking for is count the number of calls which overlap each other and report the highest figure. The current out put looks like it is adding or multiplying all of the results. This error came in at the point of adding brackets to "GROUP BY DATE(a.calldate)" – user3246938 Jan 31 '14 at 10:29
  • Reedited again - I added the GROUP BY to the outside, but forgot to remove it from the inside ... which I now finally did. – Eugen Rieck Jan 31 '14 at 10:35
  • Many thanks for taking the time to look at this. The result now is a single record with a very high count – user3246938 Jan 31 '14 at 11:17
  • I've added some sample data – user3246938 Jan 31 '14 at 12:01
  • What data type is the "calldate" field? Doesn't look like a DATETIME! – Eugen Rieck Jan 31 '14 at 13:36
  • Type is DATETIME and duration is int(11) – user3246938 Jan 31 '14 at 13:54
  • If we ran the report for 08/11/2013 the correct output should be "08/11/2013 - 7" Any thoughts? – user3246938 Feb 04 '14 at 09:57
  • How do you get 7? I thought it should be 3: the first 3 lines overlap. line 4 and 5 do not overlap, lines 6 and 7 overlap, so the max is 3 – Eugen Rieck Feb 04 '14 at 10:26
  • Yes this works perfectly. Thank you for your help. I have learned a lot. – user3246938 Feb 04 '14 at 12:03
  • I'm thinking of running this query on a daily basis just after midnight for the last day substituting the between dates with >= NOW() - INTERVAL 1 DAY. My question is how do I write the results to a separate database and table? I am trying this INSERT INTO `bridgedb`.`stats` (`date`, `peakchan`) VALUES ('calldate', 'peakcount'); – user3246938 Mar 23 '14 at 19:14
  • `INSERT INTO bridgedb.stats (date, peakchan) SELECT ... ` should do the trick. AS an alternative, you could put it into a stored procedure like `SELECT ... INTO thedate, thepeak ...; INSERT INTO bridgedb.stats (date, peakchan) VALUES (@thedate, @thepeak);` – Eugen Rieck Mar 23 '14 at 19:32
  • Yes simplifying the insert line does work, thanks. I have been trying to add a few other query's to run at the same time for example (SELECT SUM(duration) AS totalmins FROM bridgedb.log WHERE `start` >= NOW() - INTERVAL 4 DAY), (SELECT COUNT(*) FROM bridgedb.log WHERE `start` >= NOW() - INTERVAL 4 DAY); Which works well but not when I add the query discussed here. Any thoughts what I am doing wrong? I have added to the post about. Thanks – user3246938 Mar 25 '14 at 00:53
  • The way you want to assemble the different parts of a single insert from different base queries simply doesn't work that way: `INSERT INTO ... SELECT ...` works only with a single select. Use the SP approach with 4x `SELECT ... INTO ...` and a single insert. – Eugen Rieck Mar 25 '14 at 09:17
  • I'm going to create a separate question as I'm not getting this. I'm getting some results but when I try a do a stored procedure it does't work at all. Clearly my code – user3246938 Mar 25 '14 at 23:29
  • Good idea to create a separate question. Please link it here in the comments, as it is after midnight here and I am off to bed. – Eugen Rieck Mar 25 '14 at 23:32
  • http://stackoverflow.com/questions/22648582/multiple-mysql-queries-to-a-single-insert – user3246938 Mar 25 '14 at 23:38
  • You should also add `AND a.disposition='ANSWERED'` to filter calls that only rang (such as ring groups) – namezero Jun 06 '19 at 17:52
1

Try adding +1 for each start of call, and -1 for each end, then just get cumulative sum of that +1/-1 column
***Convert the calldate if needed or use your format:

set @from:='2015-02-01';
set @to:='2015-03-01';
set @csum:=0;
SELECT DT,CallCount, (@csum := @csum + CallCount) as cumulative_sum
FROM 
(select calldate AS DT, 1 AS CallCount
 from cdr
 where calldate between @from and @to

 union all

 select ADDDATE(calldate,INTERVAL duration SECOND) AS DT, -1 AS CallCount
 from cdr 
 where calldate between @from and @to
  ) Calls
ORDER BY 1 asc;
mirkobrankovic
  • 2,389
  • 1
  • 21
  • 24
0

Using Groups, have the switch write the total calls up +1 to a new CDR value for such purpose. so you know how many calls were up when the last call entered the system.

Randy
  • 1