Book a Demo

Details per agent per queue

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

• We did not include the columns Time logged on and Time in pause in the SQL query on the next page
• A phone call was not counted as ‘offered’ in the standard statistics report prior to October 2016 if the caller hung up within 10 sec, but this ‘rule’ was changed in the October 2016 release. You may change the SQL query to include all calls with result=h (not only the ones with duration>10)
• A chat/email rejected by agent in the Puzzel Agent application (result=b) is counted as ‘offered’ in the standard statistics report, but not in the query below, but you can adjust the query to include chats rejected by agents.

declare @FromDate datetime, @ToDate datetime
set @FromDate = '16-feb-2016 10:45'
set @ToDate = '17-feb-2016 11:00'

select 
coalesce(agents.user_num, convert(varchar(10),call_events.agent_id)) Id, 
agents.full_name AgentName, 
agents.usergroup_name UserGroup,
coalesce(queues.descript,call_events.queue_key) [Queue],
count(*) Offered,
sum(case when result_code = 'k' then 1 else 0 end) Answered,

convert(int,case when sum(case when result_code = 'k' then 1 else 0 end) > 0 
       then
             Round((1.0*sum(case when result_code = 'k' then (duration_tot_sec-duration_speak_sec) else 0 end)) / sum(case when result_code = 'k' then 1 else 0 end),0) 
       else 
             NULL 
       end
       ) secAvgRingTime,


convert(int, 
       case when sum(case when result_code = 'k' then 1 else 0 end) > 0 
             then
                    ROUND(1.0 * (sum(case when result_code = 'k' then duration_speak_sec else 0 end)) / sum(case when result_code = 'k' then 1 else 0 end),0 )
             else 
                    NULL 
       end
       ) secAvgSpeakTime,

convert( varchar(10), dateadd(second, case when sum(case when result_code = 'k' then 1 else 0 end) > 0 then
       Round((1.0 * sum(case when result_code = 'k' then duration_speak_sec else 0 end)) / sum(case when result_code = 'k' then 1 else 0 end), 0)
       else NULL 
       end, '1-jan-2000'),114) AvgSpeakTime,

max(duration_speak_sec) secMaxSpeakTime,

convert( varchar(10), dateadd(second, max(duration_speak_sec),'1-jan-2000'),114) MaxSpeakTime,

sum( case when call_transfer=1 then 1 else 0 end) Transfered,

convert( int, 
       case when sum(case when result_code = 'k' then 1 else 0 end) > 0
             then
                    Round( 1.0*sum(wrap_up_sec) / sum(case when result_code = 'k' then 1 else 0 end), 0)
             else
                    NULL
       end) secAvgWrapUp,

convert( int, 
       case when sum(case when result_code = 'k' then 1 else 0 end) > 0
             then
                    Round( 1.0*sum(duration_speak_sec+wrap_up_sec) / sum(case when result_code = 'k' then 1 else 0 end), 0)
             else
                    NULL
       end) secAvgAHT



from call_events 
             left outer join agents on agents.agent_id = call_events.agent_id
             left outer join queues on queues.queue_key = call_events.queue_key

where  dte_start between @FromDate and @ToDate 
             and event_type='c' and ( result_code in ('k','t')  OR  (result_code='h' AND duration_tot_sec>10) )
and          agents.agent_id = call_events.agent_id
group by call_events.agent_id, coalesce(agents.user_num, convert(varchar(10),call_events.agent_id)), 
              agents.full_name, agents.usergroup_name, coalesce(queues.descript,call_events.queue_key)
order by 1,2

Back to Top

Details per agent per queue

Recent documents

Popular documents