0

I am trying to construct a query to get concurrent calls of of my Asterisk CDR that I uploaded into MySQL.

I have tried the following examples located on these threads:
mysql query - peak concurrent calls CDR data but my results were not what I expected.
fetch max number of concurrent phone calls from call_log and this seems to take forever and the results are not what I expected either.

I cant even rewrite them because the base was not what I expected so I wouldnt even know where to start.

What I would like are the following:
1 - query for peak calls for the system as a whole for the day

2015-06-01 | 134

2 - query to get a list of times of the top 10 concurrent calls for a selected period. 

 2015-06-01 9:32:21 | 50
 2015-06-01 10:15:11 | 43
 2015-06-01 15:45:14 | 40
 .......

I have other complex queries but for now this would get me started.

Eventually, I want to get max concurrent calls of a specified day where src or dst equals a pool of specified DIDs

My MySQL database is in the format of 

source  destination     calldate    endtime     duration    uniqueid

Any help is appreciated.

Community
  • 1
  • 1
eherr9633
  • 47
  • 9

1 Answers1

0

Here is the required Query to Find max concurrent calls on a specified day.

SET @Start = '2015-12-03 00:00:00';
SET @END = '2015-12-03 23:59:59';
SET @SRC = ('DID1','DID2');
SET @DST = ('DID3','DID4');
SELECT DATE(calldate) as 'Date', 
MAX((SELECT COUNT(*) FROM cdr c2 WHERE (c2.source IN @SRC or c2.destination IN @DST ) and c2.calldate between @Start and @END AND UNIX_TIMESTAMP(c1.calldate) BETWEEN UNIX_TIMESTAMP(c2.calldate) AND (UNIX_TIMESTAMP(c2.calldate)+c2.duration))) AS 'Channels' 
FROM cdr c1 WHERE (c1.source IN @SRC or c1.destination IN @DST ) and c1.calldate between @Start and @END GROUP BY 1;

Please modify @SRC and @DST with manual addition of list of DIDs. Or you can manually add DIDs list on the places of @SRC and @DST.

Azhar Nawaz
  • 91
  • 12
  • this isnt working. I am getting an empty set. I also had an error with the @src saying operand should contain 1 column – eherr9633 Jan 15 '17 at 17:12