Details per agent 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 agent per queue is available in the Puzzel Administration Portal on page Statistics - Reports  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