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.
• 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