Two Random Asterisk-Related SQL Queries

I hate losing useful SQL queries, so I tend to save them. Ever so often, I stumble across a file of queries from some work I was doing months ago. The following two queries are just such a case. I was analyzing some call data for a call center that uses Asterisk. I set up a temporary database for this, so I can't even run them again. Still, it's nice to save them away for a rainy day.

<!--more-->

Call Center Call Volume by Day of Week and Hour

SET @tz = 'EST5EDT';
select 
  DAYNAME(CONVERT_TZ(start, 'GMT', @tz)) AS "Weekday",
  HOUR(CONVERT_TZ(start, 'GMT', @tz)) AS "Hour",
  COUNT(id), 
  SUM(duration), 
  SUM(billsec), 
  AVG(duration), AVG(billsec) 
  FROM cdr 
    WHERE dcontext IN ('Queue1', 'Queue2', 'Queue3', 'Queue4')
      AND duration > 60
    GROUP BY Weekday, Hour
    ORDER BY WEEKDAY(CONVERT_TZ(start, 'GMT', @tz)), Hour 
;

I was trying to figure out what the busy days and hours were for a call center. As you can see, only calls longer than 60 seconds counted. This was to rule out calls which hung up too quickly. I believe "duration" is the length of the entire call, including navigation through the IVR to reach the call center.

Call Center Erlangs by Weekday and Hour

SET @tz = 'EST5EDT', SET @ivr_time = 70;
SELECT 
  DAYNAME(CONVERT_TZ(start, 'GMT', @tz)) AS "Weekday",
  HOUR(CONVERT_TZ(start, 'GMT', @tz)) AS "Hour",
  SUM(billsec - @ivr_time) / 3600 / COUNT(DISTINCT(DAYOFYEAR(CONVERT_TZ(start, 'GMT', @tz)))) AS "erlangs" , 
  AVG(duration - @ivr_time), AVG(billsec - @ivr_time) 
  FROM cdr 
    WHERE dcontext IN ('Queue1', 'Queue2', 'Queue3', 'Queue4')
      AND duration > @ivr_time
    GROUP BY Weekday, Hour
    HAVING erlangs > 1
    ORDER BY WEEKDAY(CONVERT_TZ(start, 'GMT', @tz)), Hour 
;

This query is similar to the one above except it outputs Erlangs rather than call volume.

Let me know if these are useful to you.

My Bookshelf

Reading Now

Other Stuff