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.
Call Center Call Volume by Day of Week and Hour
SET @tz = 'EST5EDT';
DAYNAME(CONVERT_TZ(start, 'GMT', @tz)) AS "Weekday",
HOUR(CONVERT_TZ(start, 'GMT', @tz)) AS "Hour",
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 …[Read more]