| Excerpt |
|---|
| We want to create a report to group the calls by 3 time slots: from 00 to 08, from 09 to 16 and from 17 to 24. |
Step-by-step guide
Add a new metric:
Edit the created metric and set the query
...
| Code Block | ||||||
|---|---|---|---|---|---|---|
| ||||||
SELECT call_log.origcalldate AS "Day",
CASE
WHEN call_log.origcallhour IN ('0','1','2','3','4','5','6','7') THEN '00-08'
WHEN call_log.origcallhour IN ('8','9','10','11','12','13','14','15') THEN '08-16'
WHEN call_log.origcallhour IN ('16','17','18','19','20','21','22','23') THEN '16-24'
END AS "TimeSlot",
COUNT(IF(call_log.connect=1 AND call_log.enterqueue=1 AND call_log.ivrevent is NULL AND call_log.outboundcall=0 ,1,NULL)) AS "Answered",
COUNT(IF(call_log.abandon=1,1,NULL)) AS "Abandoned",
ROUND(AVG(IF(call_log.connect=1 AND call_log.enterqueue=1,call_log.calltime,NULL))) AS "avg Talk Time",
ROUND(AVG(IF(call_log.connect=1 AND call_log.enterqueue=1,call_log.holdtime,NULL))) AS "avg Hold Time",
ROUND(AVG(IF(call_log.connect=0 AND call_log.enterqueue=1 AND abandon=1,call_log.waittime,NULL))) AS "avg Abandon Time"
FROM call_log WHERE ( call_log.connect='1' or call_log.connect='0')
GROUP BY call_log.origcalldate,CASE
WHEN call_log.origcallhour IN ('0','1','2','3','4','5','6','7') THEN '00-08'
WHEN call_log.origcallhour IN ('8','9','10','11','12','13','14','15') THEN '08-16'
WHEN call_log.origcallhour IN ('16','17','18','19','20','21','22','23') THEN '16-24'
END
ORDER BY call_log.origcalldate ,CASE
WHEN call_log.origcallhour IN ('0','1','2','3','4','5','6','7') THEN '00-08'
WHEN call_log.origcallhour IN ('8','9','10','11','12','13','14','15') THEN '08-16'
WHEN call_log.origcallhour IN ('16','17','18','19','20','21','22','23') THEN '16-24'
END |
...