Details per queue
Please see general information about the differences in results when you compare numbers in the Puzzel standard reports (like this one) and SQL queries you use on the Raw data here.
The report description for Details per queue is available in the Puzzel Administration Portal on page Statistics - Reports, where the report is generated. The report contains 1 row per queue for media type Phone, and 1 row for call_ids that do not contain a queue event.
In the SQL query:
• We only count queue events with start in the chosen time period
(we don’t fetch speaktime or wrap-up time from conversation events “belonging” to the selected queue events or from other conversation events with start in this time period)
• We did not include column Incoming calls (which counts call_ids grouped by the call_id’s first queue event’s queue_key)
• We did not include column Answered call-backs
(it requires some logic to decide if a callback (queue event with result=q) is answered by agent and the one that ordered call-back)
• We did not include columns Avg. speaktime and Max. speaktime (since these are values from conversation events which you may think “belong” in another time period)
• We have added some extra columns for different queue event results (queue exits)
• We have only one column for Answered within n sec, and in the query we have used 10 sec:
duration_tot_sec < 10You can change from 10 sec to your value (e.g. 30) and rename the column from:
• PercAnswWithinSec10 to e.g. PercAnswWithinSec30
declare @FromDate datetime, @ToDate datetime
set @FromDate = '16-feb-2016 10:45'
set @ToDate = '16-feb-2016 11:00'
select
coalesce(queues.descript,call_events.queue_key) [Queue],
sum(case when result_code <> 'd' then 1 else 0 end) TotalCalls,
sum(case when result_code = 'h' then 1 else 0 end) HungUp,
sum(case when result_code = 'h' AND duration_tot_sec < 20 then 1 else 0 end) HungUpBlw20,
sum(case when result_code = 'q' then 1 else 0 end) CallBacks,
sum(case when result_code = 't' then 1 else 0 end) TimeOuts,
sum(case when result_code = 'a' then 1 else 0 end) Exits,
sum(case when result_code = 'k' then 1 else 0 end) Answered,
sum(case when result_code = 'd' then 1 else 0 end) Deleted,
sum(case when result_code = 's' then 1 else 0 end) Empties,
sum(case when result_code = 'e' then 1 else 0 end) Errors,
sum(case when result_code = 'f' then 1 else 0 end) Fulls,
sum(case when result_code = 'b' then 1 else 0 end) Fallbacks,
sum(case when result_code = 'c' then 1 else 0 end) Cancels,
case when ( sum( case when result_code <> 'd' then 1 else 0 end) - sum(case when result_code = 'q' then 1 else 0 end) ) > 0 then
convert( int, Round(100.0 * sum(case when result_code = 'k' then 1 else 0 end) / ( sum( case when result_code <> 'd' then 1 else 0 end) - sum(case when result_code = 'q' then 1 else 0 end) ),0) )
else
NULL
end AnswRate,
case when sum(case when result_code = 'k' then 1 else 0 end) > 0
then
convert(int, round(1.0*sum(case when result_code='k' then duration_tot_sec else 0 end) / sum(case when result_code = 'k' then 1 else 0 end),0))
else
NULL
end secAvgTimeInQAnswered,
max( case when result_code='k' then duration_tot_sec else 0 end) secMaxTimeInQAnswered,
case when sum(case when result_code = 'k' then 1 else 0 end) > 0
then
convert(int, round(1.0*sum(case when result_code='h' then duration_tot_sec else 0 end) / sum(case when result_code = 'k' then 1 else 0 end),0))
else
NULL
end secAvgTimeInQHangup,
max( case when result_code='h' then duration_tot_sec else 0 end) secMaxTimeInQHangup,
case when sum( case when result_code <> 'd' then 1 else 0 end) > 0
then
Convert(int,Round(100.0*sum(case when result_code = 'k' AND duration_tot_sec < 10 then 1 else 0 end) / sum( case when result_code <> 'd' then 1 else 0 end),0))
else
NULL
end PercAnswWithinSec10
from call_events
left outer join queues on queues.queue_key = call_events.queue_key
where dte_start between @FromDate and @ToDate
and event_type = 'q' -- queue_events
and media_type_id = 1 -- Phone only
group by coalesce(queues.descript,call_events.queue_key)
order by 1,2