Details per queue

This standard report’s description is available in the Puzzel Administration Portal on page Statistics, where the report is generated.

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 < 10

You 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

 

Published

Last updated

7
-1