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. 

declare @FromDate datetime, @ToDate datetime, @customer_key varchar(100)
set @FromDate = '2025-11-01 10:00'
set @ToDate = '2025-11-01 10:15'
set @customer_key = 'your customer key'

SELECT
   ce.customer_key,
   ce.dte_start,
   ce.queue_key,
   ce.media_type_id,
   ce.result_code,
   ce.alert_ms,
    ce.duration_tot_sec,
    ce.duration_speak_sec,
    ce.wrap_up_sec,
    ce.call_transfer,
   main_agent_id =
       CASE
           WHEN a_evt.chat_role = 1
                   AND a_evt.chat_master_user_id IS NOT NULL
               THEN a_evt.chat_master_user_id
           ELSE a_evt.agent_id
       END
into #calls
FROM call_events ce
INNER JOIN agents a_evt
   ON  a_evt.customer_key = ce.customer_key
   AND a_evt.agent_id     = ce.agent_id
WHERE
   ce.customer_key = @customer_key
   AND ce.dte_start >= @FromDate
   AND ce.dte_start <  @ToDate
   AND ce.event_type = 'c'
   AND ce.agent_id IS NOT NULL

select
    coalesce(a.user_num, convert(varchar(10),c.main_agent_id)) Id,
    a.full_name as AgentName,
    a.usergroup_name as UserGroup,
    (select top 1 descript from queues q where q.queue_key = c.queue_key) as [Queue],
    sum(case 
            when result_code in ('k','t','h') then 1 
            when result_code='b' AND isnull(isnull(alert_ms,0),0)>0 AND media_type_id = 1 then 1
            when result_code='b' AND media_type_id <> 1 then 1 
            else 0 
    end) Offered,
    sum(case when result_code = 'k' then 1 else 0 end) Answered,
    sum(case 
            when result_code = 'b' and media_type_id=1 and isnull(isnull(alert_ms,0),0)=0 then 1 
            else 0 
        end) Busy_immediately,
    sum(case 
            when result_code = 'b' and media_type_id=1 and isnull(isnull(alert_ms,0),0)>0 then 1 
            when result_code = 'b' and media_type_id<>1 then 1 
            else 0 
        end) Busy_with_alert,
    sum(case when result_code = 't' then 1 else 0 end) Alert_timeout,
    sum(case when result_code in ('q,h') then 1 else 0 end) Caller_hangup,
    sum(case when result_code = 'e' then 1 else 0 end) Error,
    sum(case when result_code = 'c' then 1 else 0 end) Setup_timeout,

    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 (isnull(duration_tot_sec,0)-isnull(duration_speak_sec,0)) 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+isnull(wrap_up_sec,0)) / sum(case when result_code = 'k' then 1 else 0 end), 0)
                 else
                        NULL
           end) secAvgAHT
from
    #calls c
        join agents a on a.agent_id = c.main_agent_id
group by
    c.main_agent_id, a.user_num, a.full_name, a.usergroup_name, c.queue_key
order by 1,2

drop table #calls