- Created by Mark Soma, last modified by Idit Adi on Mar 15, 2023
2.02 Report: Agent Time Allocation Performance
Download SQL file: 2.02-AgentTimeAllocationPerformanceSnow.sql
The example below replicates the data found in the standard 2.02 ECS report. Note that this report has two sections, a summary section and a detail section. There are two queries, one for each section.
2.02 Agent Time Allocation Performance Expand source
/* * ECS 2.02 - Agent Time Allocation Performance - report SQL for Evolve IP data warehouse * This report has two sections, summary and detail. The two queries are found below. */ /* * Summary section * * Main Time Slice, Agent Name, Total Login Time, Total Not Ready Time, Total Ready Idle Time, Total Handling Time, Average Handling Time Per Interaction, Total Busy Time, * Total No Answer Time, Total Break Time, Total Handling Outgoing Interactions Idle, Total Backoffice Idle Time, Busy To Login Time Ratio * * Consider changing iseg_startDateLocal to iseg_interactionStartDateLocal */ EXECUTE IMMEDIATE $$ -- Snowflake Scripting code BEGIN -- Report Dates LET StartDateLocal number(38, 2) := 20230302; -- Inclusive LET EndDateLocal number(38, 2) := 20230303; -- Exclusive LET res RESULTSET := (SELECT dd_dateAsDate "Date", eud_ecsUserDisplayName "Agent Name", SUM(DATEDIFF( second, alif_startTimestampLocal, alif_endTimestampLocal )) "Total Login Time", SUM(CASE WHEN alif_agentStatusName NOT IN ('Ready', 'Offered') THEN DATEDIFF( second, alif_startTimestampLocal, alif_endTimestampLocal ) ELSE 0 END) "Total Not Ready Time", SUM(CASE WHEN alif_agentStatusName IN ('Ready', 'Offered') THEN DATEDIFF( second, alif_startTimestampLocal, alif_endTimestampLocal ) ELSE 0 END) "Total Ready Idle Time", SUM(DISTINCT CASE WHEN handlingTime IS NOT NULL THEN handlingTime ELSE 0 END) "Total Handling Time", CASE WHEN SUM(DISTINCT CASE WHEN handledCount IS NOT NULL THEN handledCount ELSE 0 END) > 0 THEN SUM(DISTINCT CASE WHEN handlingTime IS NOT NULL THEN handlingTime ELSE 0 END) / SUM(DISTINCT CASE WHEN handledCount IS NOT NULL THEN handledCount ELSE 0 END) ELSE 0 END "Average Handling Time Per Interaction", SUM(CASE WHEN alif_agentStatusName LIKE 'WaitingFor%' OR alif_agentStatusName IN ('Busy', 'Dialing') THEN DATEDIFF( second, alif_startTimestampLocal, alif_endTimestampLocal ) ELSE 0 END) "Total Busy Time", SUM(CASE WHEN alif_agentStatusName = 'No Answer' THEN DATEDIFF( second, alif_startTimestampLocal, alif_endTimestampLocal ) ELSE 0 END) "Total No Answer Time", SUM(CASE WHEN alif_agentStatusName = 'Break' THEN DATEDIFF( second, alif_startTimestampLocal, alif_endTimestampLocal ) ELSE 0 END) "Total Break Time", SUM(CASE WHEN alif_agentStatusName = 'Handling - Outgoing Interactions' THEN DATEDIFF( second, alif_startTimestampLocal, alif_endTimestampLocal ) ELSE 0 END) "Total Handling Outgoing Interactions", SUM(CASE WHEN alif_agentStatusName = 'Backoffice - Administrative' THEN DATEDIFF( second, alif_startTimestampLocal, alif_endTimestampLocal ) ELSE 0 END) "Total Backoffice Time", TO_VARCHAR(CASE WHEN SUM(DATEDIFF( second, alif_startTimestampLocal, alif_endTimestampLocal )) > 0 THEN SUM(CASE WHEN alif_agentStatusName ILIKE 'WaitingFor%' OR alif_agentStatusName IN ('Busy', 'Dialing') THEN DATEDIFF( second, alif_startTimestampLocal, alif_endTimestampLocal ) ELSE 0 END) / CAST( SUM(DATEDIFF( second, alif_startTimestampLocal, alif_endTimestampLocal )) AS DECIMAL) ELSE 0 END * 100,'999') || ' %' "Busy To Login Time Ratio" FROM ECSAgentLifeFact AL LEFT OUTER JOIN DateDimension DT ON alif_startDateLocal = dd_date LEFT OUTER JOIN ECSUserDimension U ON alif_agentId = eud_id LEFT OUTER JOIN (SELECT HT.iseg_agentId, HT.iseg_startDateLocal, SUM(HT.handlingTime) AS handlingTime FROM (SELECT iseg_agentId, iseg_startDateLocal, SUM(DISTINCT iseg_agentHandlingTime) AS handlingTime FROM ECSInteractionSegmentFact WHERE iseg_agentId IS NOT NULL AND iseg_startDateLocal IS NOT NULL AND iseg_interactionStateName IN ('Handling', 'Consulting', 'In Conference') GROUP BY iseg_agentId, iseg_interactionId, iseg_startDateLocal) HT GROUP BY iseg_agentId, iseg_startDateLocal) I ON AL.alif_agentId = I.iseg_agentId AND AL.alif_startDateLocal = I.iseg_startDateLocal LEFT OUTER JOIN (SELECT iseg_agentId, iseg_startDateLocal, COUNT(handledCount) AS handledCount FROM (SELECT iseg_agentId, iseg_startDateLocal, COUNT(DISTINCT iseg_interactionId) AS handledCount FROM ECSInteractionSegmentFact WHERE iseg_agentId IS NOT NULL AND iseg_startDateLocal IS NOT NULL AND iseg_interactionStateName = 'Handling' GROUP BY iseg_agentId, iseg_interactionId, iseg_startDateLocal) HC GROUP BY iseg_agentId, iseg_startDateLocal) IC ON AL.alif_agentId = IC.iseg_agentId AND AL.alif_startDateLocal = IC.iseg_startDateLocal WHERE alif_agentLifeTypeName = 'shift' AND AL.alif_startDateLocal >= :StartDateLocal AND AL.alif_startDateLocal < :EndDateLocal GROUP BY AL.alif_agentId, dd_dateAsDate, eud_ecsUserDisplayName ORDER BY eud_ecsUserDisplayName); return TABLE(res); END; $$ ; /* * Detail section * Main Time Slice, Sub Time Slice, Total Handling Time, Average Handling Time Per Interaction * * Consider changing iseg_startDateLocal to iseg_interactionStartDateLocal */ -- Report Dates EXECUTE IMMEDIATE $$ -- Snowflake Scripting code BEGIN -- Report Dates LET StartDateLocalDetail number(38, 2) := 20230302; -- Inclusive LET EndDateLocalDetail number(38, 2) := 20230303; -- Exclusive LET res RESULTSET := (SELECT dd_dateAsDate "Date", eud_ecsUserDisplayName "Agent Name", TS.td_quarterHourLabel "Period", SUM(DISTINCT CASE WHEN handlingTime IS NOT NULL THEN handlingTime ELSE 0 END) "Total Handling Time", CASE WHEN SUM(DISTINCT CASE WHEN handledCount IS NOT NULL THEN handledCount ELSE 0 END) > 0 THEN SUM(DISTINCT CASE WHEN handlingTime IS NOT NULL THEN handlingTime ELSE 0 END) / SUM(DISTINCT CASE WHEN handledCount IS NOT NULL THEN handledCount ELSE 0 END) ELSE 0 END "Average Handling Time Per Interaction" FROM (SELECT HT.td_quarterHourLabel, HT.iseg_agentId, HT.iseg_startDateLocal, SUM(HT.handlingTime) AS handlingTime FROM (SELECT td_quarterHourLabel, iseg_agentId, iseg_startDateLocal, SUM(DISTINCT iseg_agentHandlingTime) AS handlingTime FROM TimeOfDayDimension LEFT OUTER JOIN ECSInteractionSegmentFact ON iseg_startTimeLocal <= td_lastSecondOfQuarterHour AND iseg_endTimeLocal >= td_firstSecondOfQuarterHour WHERE iseg_agentId IS NOT NULL AND iseg_startDateLocal IS NOT NULL AND iseg_interactionStateName IN ('Handling', 'Consulting', 'In Conference') AND iseg_startDateLocal >= :StartDateLocalDetail AND iseg_startDateLocal < :EndDateLocalDetail GROUP BY td_quarterHourLabel, iseg_agentId, iseg_interactionId, iseg_startDateLocal) HT GROUP BY td_quarterHourLabel, iseg_agentId, iseg_startDateLocal) TS, (SELECT HC.td_quarterHourLabel, iseg_agentId, iseg_startDateLocal, COUNT(HC.handledCount) AS handledCount FROM (SELECT td_quarterHourLabel, iseg_agentId, iseg_startDateLocal, COUNT(DISTINCT iseg_interactionId) AS handledCount FROM TimeOfDayDimension LEFT OUTER JOIN ECSInteractionSegmentFact ON iseg_startTimeLocal <= td_lastSecondOfQuarterHour AND iseg_endTimeLocal >= td_firstSecondOfQuarterHour WHERE iseg_agentId IS NOT NULL AND iseg_startDateLocal IS NOT NULL AND iseg_interactionStateName = 'Handling' AND iseg_startDateLocal >= :StartDateLocalDetail AND iseg_startDateLocal < :EndDateLocalDetail GROUP BY td_quarterHourLabel, iseg_agentId, iseg_interactionId, iseg_startDateLocal) HC GROUP BY td_quarterHourLabel, iseg_agentId, iseg_startDateLocal) TC LEFT OUTER JOIN DateDimension DT ON iseg_startDateLocal = dd_date LEFT OUTER JOIN ECSUserDimension U ON iseg_agentId = eud_id WHERE TC.td_quarterHourLabel = TS.td_quarterHourLabel AND TC.iseg_agentId = TS.iseg_agentId AND TC.iseg_startDateLocal = TS.iseg_startDateLocal GROUP BY dd_dateAsDate, eud_ecsUserDisplayName, TS.td_quarterHourLabel ORDER BY eud_ecsUserDisplayName, "Period"); return TABLE(res); END; $$ ;
2.04 Agent Interaction Summary
Download SQL file: 2.04-AgentInteractionSummarySnow.sql
The example below replicates the data found in the standard 2.04 ECS report. Note that this report has two sections, a summary section and a detail section. There are two queries, one for each section.
2.04 Agent Interaction Summary Expand source
/* * ECS 2.04 - Agent Interaction Summary - report SQL for Evolve IP data warehouse * This report has two sections, summary and detail. The two queries are found below. */ /* * Summary section * * Date, Agent, Interactions Handled, Interactions Missed, Interactions Rejected, Transferred Internal, Transferred External, Transfer %, Inbound, Outbound, Callback, Voice, Chat, Email, Ready, Break, * Busy, Staffed, No Answer, Backoffice, Handle Outgoing, Talk (Voice), Hold (Voice), WrapUp (Voice), Handle Time Voice, AHT Voice, Handle Time Chat, AHT Chat, Handle Time Email, AHT Email, Occupancy %, Utilization %, * Ready %, Talk Time % (Voice), Hold Time % (Voice), Wrap Up % (Voice), Break Time % */ -- Report Dates EXECUTE IMMEDIATE $$ -- Snowflake Scripting code BEGIN LET StartDateLocal number(38, 2) := 20220302;-- Inclusive LET EndDateLocal number(38, 2) := 20220303;-- Exclusive LET res RESULTSET := (SELECT eud_ecsUserDisplayName "Agent Name", dd_dateAsDate "Date", IFNULL(SUM(CASE WHEN iseg_interactionStateName = 'Handling' THEN 1 ELSE 0 END), 0) "Interactions Handled", IFNULL(SUM(CASE WHEN iseg_interactionStateName = 'Missed' THEN 1 ELSE 0 END), 0) "Interactions Missed", IFNULL(SUM(CASE WHEN iseg_interactionStateName = 'Rejected' THEN 1 ELSE 0 END), 0) "Interactions Rejected", IFNULL(SUM(CASE WHEN iseg_interactionStateName = 'Handling' AND iseg_endedInTransfer = 1 AND iseg_transferredIn = 1 AND iseg_transferredOut = 0 THEN 1 ELSE 0 END), 0) "Transferred Internal", IFNULL(SUM(CASE WHEN iseg_interactionStateName = 'Handling' AND iseg_endedInTransfer = 1 AND iseg_transferredIn = 0 AND iseg_transferredOut = 1 THEN 1 ELSE 0 END), 0) "Transferred External", TO_VARCHAR((IFNULL(SUM(CASE WHEN iseg_interactionStateName = 'Handling' AND iseg_endedInTransfer = 1 AND (iseg_transferredIn = 1 OR iseg_transferredOut = 1) THEN 1 ELSE 0 END) / NULLIF(CAST(SUM(CASE WHEN iseg_interactionStateName = 'Handling' THEN 1 ELSE 0 END) AS DECIMAL), 0), 0) * 100), '999.99') || ' %' "Transfer %", IFNULL(SUM(CASE WHEN iseg_interactionStateName = 'Handling' AND iseg_interactionCreationTypeName = 'Incoming' THEN 1 ELSE 0 END), 0) "Inbound", IFNULL(SUM(CASE WHEN iseg_interactionStateName = 'Handling' AND iseg_interactionCreationTypeName IN ('OutgoingExternal', 'Campaign') THEN 1 ELSE 0 END), 0) "Outbound", IFNULL(SUM(CASE WHEN iseg_interactionStateName = 'Handling' AND iseg_interactionCreationTypeName = 'Callback' THEN 1 ELSE 0 END), 0) "Callback", IFNULL(SUM(CASE WHEN iseg_interactionStateName = 'Handling' AND iseg_interactionMediaName = 'Telephony' THEN 1 ELSE 0 END), 0) "Voice", IFNULL(SUM(CASE WHEN iseg_interactionStateName = 'Handling' AND iseg_interactionMediaName = 'Chat' THEN 1 ELSE 0 END), 0) "Chat", IFNULL(SUM(CASE WHEN iseg_interactionStateName = 'Handling' AND iseg_interactionMediaName = 'Email' THEN 1 ELSE 0 END), 0) "Email", IFNULL(SUM(CASE WHEN iseg_interactionStateName = 'Handling' AND iseg_interactionMediaName = 'SMS' THEN 1 ELSE 0 END), 0) "SMS", IFNULL(AVG(AL.readyIdleTime), 0) "Ready Idle", IFNULL(AVG(AL.breakTime), 0) "Break", IFNULL(AVG(AL.busyTime), 0) "Busy", IFNULL(AVG(DATEDIFF(second, AL.staffedStart, AL.staffedEnd)), 0) "Staffed", IFNULL(AVG(AL.noAnswerTime), 0) "No Answer", IFNULL(AVG(AL.backOfficeTime), 0) "BackOffice Idle", IFNULL(AVG(AL.outgoingIdleTime), 0) "Handle Outgoing Idle", IFNULL(SUM(CASE WHEN I.iseg_InteractionMediaName = 'Telephony' THEN I.talkTime ELSE 0 END), 0) "Talk Voice", IFNULL(SUM(CASE WHEN I.iseg_InteractionMediaName = 'Telephony' AND I.iseg_interactionStateName = 'Handling' THEN I.holdTime ELSE 0 END), 0) "Hold Voice", IFNULL(SUM(CASE WHEN I.iseg_InteractionMediaName = 'Telephony' AND I.iseg_interactionStateName = 'Wrap Up' THEN I.wrapUpTime ELSE 0 END), 0) "Wrap Up Voice", IFNULL(SUM(CASE WHEN I.iseg_InteractionMediaName = 'Telephony' AND I.iseg_interactionStateName = 'Wrap Up' THEN I.wrapUpTime ELSE 0 END) + SUM(CASE WHEN I.iseg_InteractionMediaName = 'Telephony' AND I.iseg_interactionStateName IN ('Handling') THEN I.holdTime ELSE 0 END) + SUM(CASE WHEN I.iseg_InteractionMediaName = 'Telephony' THEN I.talkTime ELSE 0 END), 0) "Handle Time Voice", IFNULL(SUM(I.offeredTime) / NULLIF(SUM(I.offeredCount), 0), 0) "Avg. Offer Time", IFNULL((SUM(CASE WHEN I.iseg_InteractionMediaName = 'Telephony' AND I.iseg_interactionStateName = 'Wrap Up' THEN I.wrapUpTime ELSE 0 END) + SUM(CASE WHEN I.iseg_InteractionMediaName = 'Telephony' AND I.iseg_interactionStateName IN ('Handling') THEN I.holdTime ELSE 0 END) + SUM(CASE WHEN I.iseg_InteractionMediaName = 'Telephony' THEN I.talkTime ELSE 0 END)) / NULLIF(SUM(CASE WHEN I.iseg_InteractionMediaName = 'Telephony' AND iseg_interactionStateName = 'Handling' THEN 1 ELSE 0 END), 0), 0) "AHT Voice", IFNULL(SUM(CASE WHEN I.iseg_InteractionMediaName = 'Telephony' THEN I.offeredTime ELSE 0 END) / NULLIF(SUM(CASE WHEN I.iseg_InteractionMediaName = 'Telephony' THEN I.offeredCount ELSE 0 END), 0), 0) "Avg. Offer Time Voice", IFNULL(SUM(CASE WHEN I.iseg_InteractionMediaName = 'Chat' AND I.iseg_interactionStateName = 'Wrap Up' THEN I.wrapUpTime ELSE 0 END) + SUM(CASE WHEN I.iseg_InteractionMediaName = 'Chat' AND I.iseg_interactionStateName IN ('Handling') THEN I.holdTime ELSE 0 END) + SUM(CASE WHEN I.iseg_InteractionMediaName = 'Chat' THEN I.talkTime ELSE 0 END), 0) "Handle Time Chat", IFNULL((SUM(CASE WHEN I.iseg_InteractionMediaName = 'Chat' AND I.iseg_interactionStateName = 'Wrap Up' THEN I.wrapUpTime ELSE 0 END) + SUM(CASE WHEN I.iseg_InteractionMediaName = 'Chat' AND I.iseg_interactionStateName IN ('Handling') THEN I.holdTime ELSE 0 END) + SUM(CASE WHEN I.iseg_InteractionMediaName = 'Chat' THEN I.talkTime ELSE 0 END)) / NULLIF(SUM(CASE WHEN I.iseg_InteractionMediaName = 'Chat' AND iseg_interactionStateName = 'Handling' THEN 1 ELSE 0 END), 0), 0) "AHT Chat", IFNULL(SUM(CASE WHEN I.iseg_InteractionMediaName = 'Chat' THEN I.offeredTime ELSE 0 END) / NULLIF(SUM(CASE WHEN I.iseg_InteractionMediaName = 'Chat' THEN I.offeredCount ELSE 0 END), 0), 0) "Avg. Offer Time Chat", IFNULL(SUM(CASE WHEN I.iseg_InteractionMediaName = 'Email' AND I.iseg_interactionStateName = 'Wrap Up' THEN I.wrapUpTime ELSE 0 END) + SUM(CASE WHEN I.iseg_InteractionMediaName = 'Email' AND I.iseg_interactionStateName IN ('Handling') THEN I.holdTime ELSE 0 END) + SUM(CASE WHEN I.iseg_InteractionMediaName = 'Email' THEN I.talkTime ELSE 0 END), 0) "Handle Time Email", IFNULL((SUM(CASE WHEN I.iseg_InteractionMediaName = 'Email' AND I.iseg_interactionStateName = 'Wrap Up' THEN I.wrapUpTime ELSE 0 END) + SUM(CASE WHEN I.iseg_InteractionMediaName = 'Email' AND I.iseg_interactionStateName IN ('Handling') THEN I.holdTime ELSE 0 END) + SUM(CASE WHEN I.iseg_InteractionMediaName = 'Email' THEN I.talkTime ELSE 0 END)) / NULLIF(SUM(CASE WHEN I.iseg_InteractionMediaName = 'Email' AND iseg_interactionStateName = 'Handling' THEN 1 ELSE 0 END), 0), 0) "AHT Email", IFNULL(SUM(CASE WHEN I.iseg_InteractionMediaName = 'Email' THEN I.offeredTime ELSE 0 END) / NULLIF(SUM(CASE WHEN I.iseg_InteractionMediaName = 'Email' THEN I.offeredCount ELSE 0 END), 0), 0) "Avg. Offer Time Email", IFNULL(SUM(CASE WHEN I.iseg_InteractionMediaName = 'SMS' AND I.iseg_interactionStateName = 'Wrap Up' THEN I.wrapUpTime ELSE 0 END) + SUM(CASE WHEN I.iseg_InteractionMediaName = 'SMS' AND I.iseg_interactionStateName IN ('Handling') THEN I.holdTime END) + SUM(CASE WHEN I.iseg_InteractionMediaName = 'SMS' THEN I.talkTime ELSE 0 END), 0) "Handle Time SMS", IFNULL((SUM(CASE WHEN I.iseg_InteractionMediaName = 'SMS' AND I.iseg_interactionStateName = 'Wrap Up' THEN I.wrapUpTime ELSE 0 END) + SUM(CASE WHEN I.iseg_InteractionMediaName = 'SMS' AND I.iseg_interactionStateName IN ('Handling') THEN I.holdTime ELSE 0 END) + SUM(CASE WHEN I.iseg_InteractionMediaName = 'SMS' THEN I.talkTime ELSE 0 END)) / NULLIF(SUM(CASE WHEN I.iseg_InteractionMediaName = 'SMS' AND iseg_interactionStateName = 'Handling' THEN 1 ELSE 0 END), 0), 0) "AHT SMS", IFNULL(SUM(CASE WHEN I.iseg_InteractionMediaName = 'SMS' THEN I.offeredTime ELSE 0 END) / NULLIF(SUM(CASE WHEN I.iseg_InteractionMediaName = 'SMS' THEN I.offeredCount ELSE 0 END), 0), 0) "Avg. Offer Time SMS", TO_VARCHAR((IFNULL(AVG(AL.busyTime) / CAST(NULLIF(AVG(AL.readyIdleTime) + AVG(AL.busyTime), 0) AS DECIMAL), 0) * 100), '999.99') || ' %' "Occupancy %", TO_VARCHAR((IFNULL((AVG(AL.busyTime) + AVG(AL.readyIdleTime)) / CAST(NULLIF(AVG(DATEDIFF(second, AL.staffedStart, AL.staffedEnd)), 0) AS DECIMAL), 0) * 100), '999.99') || ' %' "Utilization %", TO_VARCHAR((IFNULL(AVG(AL.readyIdleTime) / CAST(NULLIF(AVG(DATEDIFF(second, AL.staffedStart, AL.staffedEnd)), 0) AS DECIMAL), 0) * 100), '999.99') || ' %' "Ready %", TO_VARCHAR((IFNULL(SUM(CASE WHEN I.iseg_InteractionMediaName = 'Telephony' THEN I.talkTime END) / CAST(NULLIF(SUM(CASE WHEN I.iseg_InteractionMediaName = 'Telephony' AND I.iseg_interactionStateName = 'Wrap Up' THEN I.wrapUpTime END) + SUM(CASE WHEN I.iseg_InteractionMediaName = 'Telephony' AND I.iseg_interactionStateName IN ('Handling') THEN I.holdTime END) + SUM(CASE WHEN I.iseg_InteractionMediaName = 'Telephony' THEN I.talkTime END), 0) AS DECIMAL), 0) * 100), '999.99') || ' %' "Talk Time % (Voice)", TO_VARCHAR((IFNULL(SUM(CASE WHEN I.iseg_InteractionMediaName = 'Telephony' AND I.iseg_interactionStateName = 'Handling' THEN I.holdTime END) / CAST(NULLIF(SUM(CASE WHEN I.iseg_InteractionMediaName = 'Telephony' AND I.iseg_interactionStateName = 'Wrap Up' THEN I.wrapUpTime END) + SUM(CASE WHEN I.iseg_InteractionMediaName = 'Telephony' AND I.iseg_interactionStateName IN ('Handling') THEN I.holdTime END) + SUM(CASE WHEN I.iseg_InteractionMediaName = 'Telephony' THEN I.talkTime END), 0) AS DECIMAL), 0) * 100), '999.99') || ' %' "Hold Time % (Voice)", TO_VARCHAR((IFNULL(SUM(CASE WHEN I.iseg_InteractionMediaName = 'Telephony' AND I.iseg_interactionStateName = 'Wrap Up' THEN I.wrapUpTime END) / CAST(NULLIF(SUM(CASE WHEN I.iseg_InteractionMediaName = 'Telephony' AND I.iseg_interactionStateName = 'Wrap Up' THEN I.wrapUpTime END) + SUM(CASE WHEN I.iseg_InteractionMediaName = 'Telephony' AND I.iseg_interactionStateName IN ('Handling') THEN I.holdTime END) + SUM(CASE WHEN I.iseg_InteractionMediaName = 'Telephony' THEN I.talkTime END), 0) AS DECIMAL), 0) * 100), '999.99') || ' %'"Wrap Up % (Voice)", TO_VARCHAR((IFNULL((AVG(AL.breakTime) + AVG(AL.noAnswerTime)) / CAST(NULLIF(AVG(DATEDIFF(second, AL.staffedStart, AL.staffedEnd)), 0) AS DECIMAL), 0) * 100), '999.99') || ' %' "Break Time %" FROM (SELECT DISTINCT alif_agentId, alif_startDateLocal, SUM(CASE WHEN alif_agentStatusName IN ('Ready', 'Offered') THEN DATEDIFF( second, alif_startTimestampLocal, alif_endTimestampLocal ) ELSE 0 END) readyIdleTime, SUM(CASE WHEN alif_agentStatusName IN ('Break') THEN DATEDIFF( second, alif_startTimestampLocal, alif_endTimestampLocal ) ELSE 0 END) breakTime, SUM(CASE WHEN alif_agentStatusName LIKE 'WaitingFor%' OR alif_agentStatusName IN ('Busy', 'Dialing') OR (alif_agentStatusName = 'Backoffice - Administrative' AND alif_interactionStartTime IS NOT NULL) THEN DATEDIFF( second, alif_startTimestampLocal, alif_endTimestampLocal ) ELSE 0 END) busyTime, SUM(CASE WHEN alif_agentStatusName = 'No Answer' THEN DATEDIFF( second, alif_startTimestampLocal, alif_endTimestampLocal ) ELSE 0 END) noAnswerTime, SUM(CASE WHEN alif_agentStatusName = 'Backoffice - Administrative' THEN DATEDIFF( second, alif_startTimestampLocal, alif_endTimestampLocal ) ELSE 0 END) backOfficeTime, SUM(CASE WHEN alif_agentStatusName = 'Handling - Outgoing Interactions' THEN DATEDIFF( second, alif_startTimestampLocal, alif_endTimestampLocal ) ELSE 0 END) outgoingIdleTime, MIN(CASE WHEN alif_agentStatusName <> 'Offline' THEN alif_startTimestampLocal END) staffedStart, MAX(CASE WHEN alif_agentStatusName <> 'Offline' THEN alif_endTimestampLocal END) staffedEnd FROM ECSAgentLifeFact WHERE alif_agentLifeTypeId = 1 AND alif_startDateLocal >= :StartDateLocal AND alif_startDateLocal < :EndDateLocal GROUP BY alif_agentId, alif_startDateLocal) AL LEFT OUTER JOIN DateDimension DT ON alif_startDateLocal = dd_date LEFT OUTER JOIN ECSUserDimension U ON alif_agentId = eud_id LEFT OUTER JOIN (SELECT iseg_agentID, iseg_interactionStartDateLocal, iseg_interactionCreationTypeName, iseg_interactionStateName, iseg_interactionMediaName, iseg_endedInTransfer, iseg_transferredIn, iseg_transferredOut, iseg_interactionId, SUM(iseg_talkTime) talkTime, SUM(iseg_holdTime) holdTime, SUM(iseg_wrapUpTime) wrapUpTime, SUM(CASE WHEN iseg_interactionStateName = 'Offered' THEN iseg_duration ELSE 0 END) offeredTime, SUM(CASE WHEN iseg_interactionStateName = 'Offered' THEN 1 ELSE 0 END) offeredCount FROM ECSInteractionSegmentFact WHERE iseg_agentId IS NOT NULL AND iseg_interactionStartDateLocal >= :StartDateLocal AND iseg_interactionStartDateLocal < :EndDateLocal GROUP BY iseg_agentID, iseg_interactionStartDateLocal, iseg_interactionCreationTypeName, iseg_interactionStateName, iseg_interactionMediaName, iseg_endedInTransfer, iseg_transferredIn, iseg_transferredOut, iseg_interactionId) I ON iseg_agentID = AL.alif_agentId AND iseg_interactionStartDateLocal = AL.alif_startDateLocal GROUP BY dd_dateAsDate, eud_ecsUserDisplayName ORDER BY eud_ecsUserDisplayName, dd_dateAsDate); return TABLE(res); END; $$ ; /* * Detail section * * Date, Agent, Interval, Interactions Handled, Interactions Missed, Interactions Rejected, Transferred Internal, Transferred External, Transfer %, Inbound, Outbound, Callback, Voice, Chat, Email, Ready, Break, * Busy, Staffed, No Answer, Backoffice, Handle Outgoing, Talk (Voice), Hold (Voice), WrapUp (Voice), Handle Time Voice, AHT Voice, Handle Time Chat, AHT Chat, Handle Time Email, AHT Email, Occupancy %, Utilization %, * Ready %, Talk Time % (Voice), Hold Time % (Voice), Wrap Up % (Voice), Break Time % */ -- Report Dates and segment length in seconds EXECUTE IMMEDIATE $$ -- Snowflake Scripting code BEGIN LET StartDateLocalDetail number(38, 2) := 20220302;-- Inclusive LET EndDateLocalDetail number(38, 2) := 20220303;-- Exclusive LET SecondsInPeriod number(38, 2) := 3600; -- How many second in the reporting period LET res RESULTSET := (SELECT eud_ecsUserDisplayName "Agent Name", dd_dateAsDate "Date", AL.td_hourLabel "Interval", IFNULL(SUM(CASE WHEN iseg_interactionStateName = 'Handling' THEN 1 ELSE 0 END), 0) "Interactions Handled", IFNULL(SUM(CASE WHEN iseg_interactionStateName = 'Missed' THEN 1 ELSE 0 END), 0) "Interactions Missed", IFNULL(SUM(CASE WHEN iseg_interactionStateName = 'Rejected' THEN 1 ELSE 0 END), 0) "Interactions Rejected", IFNULL(SUM(CASE WHEN iseg_interactionStateName = 'Handling' AND iseg_endedInTransfer = 1 AND iseg_transferredIn = 1 AND iseg_transferredOut = 0 THEN 1 ELSE 0 END), 0) "Transferred Internal", IFNULL(SUM(CASE WHEN iseg_interactionStateName = 'Handling' AND iseg_endedInTransfer = 1 AND iseg_transferredIn = 0 AND iseg_transferredOut = 1 THEN 1 ELSE 0 END), 0) "Transferred External", TO_VARCHAR((IFNULL(SUM(CASE WHEN iseg_interactionStateName = 'Handling' AND iseg_endedInTransfer = 1 AND (iseg_transferredIn = 1 OR iseg_transferredOut = 1) THEN 1 ELSE 0 END) / NULLIF(CAST(SUM(CASE WHEN iseg_interactionStateName = 'Handling' THEN 1 ELSE 0 END) AS DECIMAL), 0), 0)* 100), '999.99') || ' %' "Transfer %", IFNULL(SUM(CASE WHEN iseg_interactionStateName = 'Handling' AND iseg_interactionCreationTypeName = 'Incoming' THEN 1 ELSE 0 END), 0) "Inbound", IFNULL(SUM(CASE WHEN iseg_interactionStateName = 'Handling' AND iseg_interactionCreationTypeName IN ('OutgoingExternal', 'Campaign') THEN 1 ELSE 0 END), 0) "Outbound", IFNULL(SUM(CASE WHEN iseg_interactionStateName = 'Handling' AND iseg_interactionCreationTypeName = 'Callback' THEN 1 ELSE 0 END), 0) "Callback", IFNULL(SUM(CASE WHEN iseg_interactionStateName = 'Handling' AND iseg_interactionMediaName = 'Telephony' THEN 1 ELSE 0 END), 0) "Voice", IFNULL(SUM(CASE WHEN iseg_interactionStateName = 'Handling' AND iseg_interactionMediaName = 'Chat' THEN 1 ELSE 0 END), 0) "Chat", IFNULL(SUM(CASE WHEN iseg_interactionStateName = 'Handling' AND iseg_interactionMediaName = 'Email' THEN 1 ELSE 0 END), 0) "Email", IFNULL(SUM(CASE WHEN iseg_interactionStateName = 'Handling' AND iseg_interactionMediaName = 'SMS' THEN 1 ELSE 0 END), 0) "SMS", IFNULL(ROUND(AVG(AL.readyIdleTime), 0), 2) "Ready Idle", IFNULL(AVG(AL.breakTime), 0) "Break", IFNULL(AVG(AL.busyTime), 0) "Busy", IFNULL(LEAST(:SecondsInPeriod, AVG(DATEDIFF(second, AL.staffedStart, AL.staffedEnd))), 0) "Staffed", IFNULL(AVG(AL.noAnswerTime), 0) "No Answer", IFNULL(AVG(AL.backOfficeTime), 0) "BackOffice Idle", IFNULL(AVG(AL.outgoingIdleTime), 0) "Handle Outgoing Idle", IFNULL(SUM(CASE WHEN I.iseg_InteractionMediaName = 'Telephony' THEN I.talkTime ELSE 0 END), 0) "Talk Voice", IFNULL(SUM(CASE WHEN I.iseg_InteractionMediaName = 'Telephony' AND I.iseg_interactionStateName = 'Handling' THEN I.holdTime ELSE 0 END), 0) "Hold Voice", IFNULL(SUM(CASE WHEN I.iseg_InteractionMediaName = 'Telephony' AND I.iseg_interactionStateName = 'Wrap Up' THEN I.wrapUpTime ELSE 0 END), 0) "Wrap Up Voice", IFNULL(SUM(CASE WHEN I.iseg_InteractionMediaName = 'Telephony' AND I.iseg_interactionStateName = 'Wrap Up' THEN I.wrapUpTime ELSE 0 END) + SUM(CASE WHEN I.iseg_InteractionMediaName = 'Telephony' AND I.iseg_interactionStateName IN ('Handling') THEN I.holdTime ELSE 0 END) + SUM(CASE WHEN I.iseg_InteractionMediaName = 'Telephony' THEN I.talkTime ELSE 0 END), 0) "Handle Time Voice", IFNULL(ROUND(SUM(I.offeredTime) / NULLIF(SUM(I.offeredCount), 0), 0), 2) "Avg. Offer Time", IFNULL(ROUND((SUM(CASE WHEN I.iseg_InteractionMediaName = 'Telephony' AND I.iseg_interactionStateName = 'Wrap Up' THEN I.wrapUpTime ELSE 0 END) + SUM(CASE WHEN I.iseg_InteractionMediaName = 'Telephony' AND I.iseg_interactionStateName IN ('Handling') THEN I.holdTime ELSE 0 END) + SUM(CASE WHEN I.iseg_InteractionMediaName = 'Telephony' THEN I.talkTime ELSE 0 END)) / NULLIF(SUM(CASE WHEN I.iseg_InteractionMediaName = 'Telephony' AND iseg_interactionStateName = 'Handling' THEN 1 ELSE 0 END), 0), 0) , 2) "AHT Voice", IFNULL(ROUND(SUM(CASE WHEN I.iseg_InteractionMediaName = 'Telephony' THEN I.offeredTime ELSE 0 END) / NULLIF(SUM(CASE WHEN I.iseg_InteractionMediaName = 'Telephony' THEN I.offeredCount ELSE 0 END), 0), 0), 2) "Avg. Offer Time Voice", IFNULL(SUM(CASE WHEN I.iseg_InteractionMediaName = 'Chat' AND I.iseg_interactionStateName = 'Wrap Up' THEN I.wrapUpTime ELSE 0 END) + SUM(CASE WHEN I.iseg_InteractionMediaName = 'Chat' AND I.iseg_interactionStateName IN ('Handling') THEN I.holdTime ELSE 0 END) + SUM(CASE WHEN I.iseg_InteractionMediaName = 'Chat' THEN I.talkTime ELSE 0 END), 0) "Handle Time Chat", IFNULL((SUM(CASE WHEN I.iseg_InteractionMediaName = 'Chat' AND I.iseg_interactionStateName = 'Wrap Up' THEN I.wrapUpTime ELSE 0 END) + SUM(CASE WHEN I.iseg_InteractionMediaName = 'Chat' AND I.iseg_interactionStateName IN ('Handling') THEN I.holdTime ELSE 0 END) + SUM(CASE WHEN I.iseg_InteractionMediaName = 'Chat' THEN I.talkTime ELSE 0 END)) / NULLIF(SUM(CASE WHEN I.iseg_InteractionMediaName = 'Chat' AND iseg_interactionStateName = 'Handling' THEN 1 ELSE 0 END), 0), 0) "AHT Chat", IFNULL(SUM(CASE WHEN I.iseg_InteractionMediaName = 'Chat' THEN I.offeredTime ELSE 0 END) / NULLIF(SUM(CASE WHEN I.iseg_InteractionMediaName = 'Chat' THEN I.offeredCount ELSE 0 END), 0), 0) "Avg. Offer Time Chat", IFNULL(SUM(CASE WHEN I.iseg_InteractionMediaName = 'Email' AND I.iseg_interactionStateName = 'Wrap Up' THEN I.wrapUpTime ELSE 0 END) + SUM(CASE WHEN I.iseg_InteractionMediaName = 'Email' AND I.iseg_interactionStateName IN ('Handling') THEN I.holdTime ELSE 0 END) + SUM(CASE WHEN I.iseg_InteractionMediaName = 'Email' THEN I.talkTime ELSE 0 END), 0) "Handle Time Email", IFNULL((SUM(CASE WHEN I.iseg_InteractionMediaName = 'Email' AND I.iseg_interactionStateName = 'Wrap Up' THEN I.wrapUpTime ELSE 0 END) + SUM(CASE WHEN I.iseg_InteractionMediaName = 'Email' AND I.iseg_interactionStateName IN ('Handling') THEN I.holdTime ELSE 0 END) + SUM(CASE WHEN I.iseg_InteractionMediaName = 'Email' THEN I.talkTime ELSE 0 END)) / NULLIF(SUM(CASE WHEN I.iseg_InteractionMediaName = 'Email' AND iseg_interactionStateName = 'Handling' THEN 1 ELSE 0 END), 0), 0) "AHT Email", IFNULL(SUM(CASE WHEN I.iseg_InteractionMediaName = 'Email' THEN I.offeredTime ELSE 0 END) / NULLIF(SUM(CASE WHEN I.iseg_InteractionMediaName = 'Email' THEN I.offeredCount ELSE 0 END), 0), 0) "Avg. Offer Time Email", IFNULL(SUM(CASE WHEN I.iseg_InteractionMediaName = 'SMS' AND I.iseg_interactionStateName = 'Wrap Up' THEN I.wrapUpTime ELSE 0 END) + SUM(CASE WHEN I.iseg_InteractionMediaName = 'SMS' AND I.iseg_interactionStateName IN ('Handling') THEN I.holdTime END) + SUM(CASE WHEN I.iseg_InteractionMediaName = 'SMS' THEN I.talkTime ELSE 0 END), 0) "Handle Time SMS", IFNULL((SUM(CASE WHEN I.iseg_InteractionMediaName = 'SMS' AND I.iseg_interactionStateName = 'Wrap Up' THEN I.wrapUpTime ELSE 0 END) + SUM(CASE WHEN I.iseg_InteractionMediaName = 'SMS' AND I.iseg_interactionStateName IN ('Handling') THEN I.holdTime ELSE 0 END) + SUM(CASE WHEN I.iseg_InteractionMediaName = 'SMS' THEN I.talkTime ELSE 0 END)) / NULLIF(SUM(CASE WHEN I.iseg_InteractionMediaName = 'SMS' AND iseg_interactionStateName = 'Handling' THEN 1 ELSE 0 END), 0), 0) "AHT SMS", IFNULL(SUM(CASE WHEN I.iseg_InteractionMediaName = 'SMS' THEN I.offeredTime ELSE 0 END) / NULLIF(SUM(CASE WHEN I.iseg_InteractionMediaName = 'SMS' THEN I.offeredCount ELSE 0 END), 0), 0) "Avg. Offer Time SMS", TO_VARCHAR((IFNULL(AVG(AL.busyTime) / CAST(NULLIF(AVG(AL.readyIdleTime) + AVG(AL.busyTime), 0) AS DECIMAL), 0)* 100), '999.99') || ' %' "Occupancy %", TO_VARCHAR((IFNULL((AVG(AL.busyTime) + AVG(AL.readyIdleTime)) / CAST(NULLIF(AVG(DATEDIFF(second, AL.staffedStart, AL.staffedEnd)), 0) AS DECIMAL), 0)* 100), '999.99') || ' %' "Utilization %", TO_VARCHAR((IFNULL(AVG(AL.readyIdleTime) / CAST(NULLIF(AVG(DATEDIFF(second, AL.staffedStart, AL.staffedEnd)), 0) AS DECIMAL), 0)* 100), '999.99') || ' %' "Ready %", TO_VARCHAR((IFNULL(SUM(CASE WHEN I.iseg_InteractionMediaName = 'Telephony' THEN I.talkTime END) / CAST(NULLIF(SUM(CASE WHEN I.iseg_InteractionMediaName = 'Telephony' AND I.iseg_interactionStateName = 'Wrap Up' THEN I.wrapUpTime END) + SUM(CASE WHEN I.iseg_InteractionMediaName = 'Telephony' AND I.iseg_interactionStateName IN ('Handling') THEN I.holdTime END) + SUM(CASE WHEN I.iseg_InteractionMediaName = 'Telephony' THEN I.talkTime END), 0) AS DECIMAL), 0)* 100), '999.99') || ' %' "Talk Time % (Voice)", TO_VARCHAR((IFNULL(SUM(CASE WHEN I.iseg_InteractionMediaName = 'Telephony' AND I.iseg_interactionStateName = 'Handling' THEN I.holdTime END) / CAST(NULLIF(SUM(CASE WHEN I.iseg_InteractionMediaName = 'Telephony' AND I.iseg_interactionStateName = 'Wrap Up' THEN I.wrapUpTime END) + SUM(CASE WHEN I.iseg_InteractionMediaName = 'Telephony' AND I.iseg_interactionStateName IN ('Handling') THEN I.holdTime END) + SUM(CASE WHEN I.iseg_InteractionMediaName = 'Telephony' THEN I.talkTime END), 0) AS DECIMAL), 0)* 100), '999.99') || ' %' "Hold Time % (Voice)", TO_VARCHAR((IFNULL(SUM(CASE WHEN I.iseg_InteractionMediaName = 'Telephony' AND I.iseg_interactionStateName = 'Wrap Up' THEN I.wrapUpTime END) / CAST(NULLIF(SUM(CASE WHEN I.iseg_InteractionMediaName = 'Telephony' AND I.iseg_interactionStateName = 'Wrap Up' THEN I.wrapUpTime END) + SUM(CASE WHEN I.iseg_InteractionMediaName = 'Telephony' AND I.iseg_interactionStateName IN ('Handling') THEN I.holdTime END) + SUM(CASE WHEN I.iseg_InteractionMediaName = 'Telephony' THEN I.talkTime END), 0) AS DECIMAL), 0)* 100), '999.99') || ' %' "Wrap Up % (Voice)", TO_VARCHAR((IFNULL((AVG(AL.breakTime) + AVG(AL.noAnswerTime)) / CAST(NULLIF(AVG(DATEDIFF(second, AL.staffedStart, AL.staffedEnd)), 0) AS DECIMAL), 0)* 100), '999.99') "Break Time %" FROM (SELECT DISTINCT td_hourLabel, alif_agentId, alif_startDateLocal, LEAST(:SecondsInPeriod, SUM(readyIdleTime)) AS readyIdleTime, LEAST(:SecondsInPeriod, SUM(breakTime)) AS breakTime, LEAST(:SecondsInPeriod, SUM(busyTime)) AS busyTime, LEAST(:SecondsInPeriod, SUM(noAnswerTime)) AS noAnswerTime, LEAST(:SecondsInPeriod, SUM(backOfficeTime)) AS backOfficeTime, LEAST(:SecondsInPeriod, SUM(outgoingIdleTime)) AS outgoingIdleTime, MIN(staffedStart) AS staffedStart, MAX(staffedEnd) AS staffedEnd FROM (SELECT DISTINCT td_hourLabel, alif_agentId, alif_startDateLocal, alif_shiftId, alif_startTimestampLocal, alif_endTimestampLocal, CASE WHEN alif_agentStatusName IN ('Ready', 'Offered') THEN DATEDIFF( second, alif_startTimestampLocal, alif_endTimestampLocal ) ELSE 0 END readyIdleTime, CASE WHEN alif_agentStatusName IN ('Break') THEN DATEDIFF( second, alif_startTimestampLocal, alif_endTimestampLocal ) ELSE 0 END breakTime, CASE WHEN alif_agentStatusName LIKE 'WaitingFor%' OR alif_agentStatusName IN ('Busy', 'Dialing') OR (alif_agentStatusName = 'Backoffice - Administrative' AND alif_interactionStartTime IS NOT NULL) THEN DATEDIFF( second, alif_startTimestampLocal, alif_endTimestampLocal ) ELSE 0 END busyTime, CASE WHEN alif_agentStatusName = 'No Answer' THEN DATEDIFF( second, alif_startTimestampLocal, alif_endTimestampLocal ) ELSE 0 END noAnswerTime, CASE WHEN alif_agentStatusName = 'Backoffice - Administrative' THEN DATEDIFF( second, alif_startTimestampLocal, alif_endTimestampLocal ) ELSE 0 END backOfficeTime, CASE WHEN alif_agentStatusName = 'Handling - Outgoing Interactions' THEN DATEDIFF( second, alif_startTimestampLocal, alif_endTimestampLocal ) ELSE 0 END outgoingIdleTime, CASE WHEN alif_agentStatusName <> 'Offline' THEN alif_startTimestampLocal END staffedStart, CASE WHEN alif_agentStatusName <> 'Offline' THEN alif_endTimestampLocal END staffedEnd FROM ECSAgentLifeFact INNER JOIN TimeOfDayDimension ON alif_startTimeLocal <= td_lastSecondOfhour AND alif_endTimeLocal >= td_firstSecondOfhour WHERE alif_agentLifeTypeId = 1 AND alif_startDateLocal >= :StartDateLocalDetail AND alif_startDateLocal < :EndDateLocalDetail) IAL GROUP BY alif_startDateLocal, alif_agentId, td_hourLabel) AL LEFT OUTER JOIN DateDimension DT ON alif_startDateLocal = dd_date LEFT OUTER JOIN ECSUserDimension U ON alif_agentId = eud_id, (SELECT DISTINCT td_hourLabel, iseg_agentID, iseg_interactionStartDateLocal, iseg_interactionId, iseg_interactionCreationTypeName, iseg_interactionStateName, iseg_interactionMediaName, iseg_endedInTransfer, iseg_transferredIn, iseg_transferredOut, LEAST(:SecondsInPeriod, SUM(talkTime)) talkTime, LEAST(:SecondsInPeriod, SUM(holdTime)) holdTime, LEAST(:SecondsInPeriod, SUM(wrapUpTime)) wrapUpTime, LEAST(:SecondsInPeriod, SUM(offeredTime)) offeredTime, LEAST(:SecondsInPeriod, SUM(offeredCount)) offeredCount FROM (SELECT DISTINCT td_hourLabel, iseg_agentID, iseg_interactionStartDateLocal, iseg_interactionId, iseg_interactionCreationTypeName, iseg_interactionStateName, iseg_interactionMediaName, iseg_endedInTransfer, iseg_transferredIn, iseg_transferredOut, iseg_talkTime talkTime, iseg_holdTime holdTime, iseg_wrapUpTime wrapUpTime, CASE WHEN iseg_interactionStateName = 'Offered' THEN iseg_duration ELSE 0 END offeredTime, CASE WHEN iseg_interactionStateName = 'Offered' THEN 1 ELSE 0 END offeredCount FROM TimeOfDayDimension LEFT OUTER JOIN ECSInteractionSegmentFact ON iseg_interactionStartTimeLocal <= td_lastSecondOfhour AND iseg_interactionStartTimeLocal >= td_firstSecondOfhour WHERE iseg_agentId IS NOT NULL AND iseg_interactionStartDateLocal >= :StartDateLocalDetail AND iseg_interactionStartDateLocal < :EndDateLocalDetail) II GROUP BY iseg_interactionStartDateLocal, iseg_agentID, td_hourLabel, iseg_interactionId, iseg_interactionCreationTypeName, iseg_interactionStateName, iseg_interactionMediaName, iseg_endedInTransfer, iseg_transferredIn, iseg_transferredOut, iseg_interactionId) I WHERE AL.td_hourLabel = I.td_hourLabel AND AL.alif_agentId = I.iseg_agentId AND AL.alif_startDateLocal = I.iseg_interactionStartDateLocal GROUP BY dd_dateAsDate, eud_ecsUserDisplayName, AL.td_hourLabel ORDER BY eud_ecsUserDisplayName, dd_dateAsDate, AL.td_hourLabel); return TABLE(res); END; $$ ;
5.05 Queue Interval Summary Report
Download SQL file: 5.05-QueueIntervalSummaryReportSnow.sql
The example below replicates the data found in the standard 5.05 ECS report.
5.05 Queue Interval Summary Report Expand source
/* * ECS 5.05 - Queue Interval Summary Report - report SQL for Evolve IP data warehouse */ SELECT dd_dateAsDate "Date", TOD.td_halfHourLabel "Time Slice", COUNT(*) AS "Calls Queued" ,SUM(CAST(intr_answered AS INTEGER)) AS "Calls Answered" ,SUM(CASE WHEN A.abandonedQueueTime <= 30 THEN 1 ELSE 0 END) AS "Calls Abandoned 0-30 sec" ,SUM(CASE WHEN A.abandonedQueueTime > 30 THEN 1 ELSE 0 END) AS "Calls Abandoned > 30 sec" ,CASE WHEN COUNT(*) > 0 THEN TO_VARCHAR((CAST(COUNT(A.abandonedQueueTime) AS DECIMAL) / COUNT(*)) * 100, '999') || ' %' ELSE 'N/A' END AS "% Total Abandoned Calls" ,CASE WHEN COUNT(*) > 0 THEN TO_VARCHAR((CAST(SUM(CASE WHEN A.abandonedQueueTime > 30 THEN 1 ELSE 0 END) AS DECIMAL) / COUNT(*)) * 100, '999') || ' %' ELSE 'N/A' END AS "% Total Abandoned Calls > 30 sec" ,SUM(intr_queueTime) AS "Total Waiting Time" ,ROUND(AVG(intr_queueTime), 2) AS "Average Waiting Time" ,SUM(R.selectDuration) AS "Total Selection Time" ,SUM(CASE WHEN intr_queueTime <= 300 THEN 1 ELSE 0 END) AS "Queue Time 0-300 sec" ,SUM(CASE WHEN intr_queueTime > 300 AND intr_queueTime <= 600 THEN 1 ELSE 0 END) AS "Queue Time 301-600 sec" ,SUM(CASE WHEN intr_queueTime > 600 THEN 1 ELSE 0 END) AS "Queue Time > 600 sec" ,CASE WHEN SUM(CAST(intr_answered AS INTEGER) - CAST(intr_rejected AS INTEGER)) > 0 THEN TO_CHAR(ROUND((CAST (SUM(CASE WHEN intr_answered = 1 AND intr_rejected = 0 THEN intr_queueTime ELSE 0 END) AS DECIMAL) / SUM(CASE WHEN intr_answered = 1 AND intr_rejected = 0 THEN 1 ELSE 0 END)), 2)) ELSE 'N/A' END AS "Average Speed of Answer" ,MAX(CASE WHEN intr_answered = 1 AND intr_rejected = 0 THEN intr_queueTime ELSE 0 END) AS "Maximum Speed of Answer" ,SUM(intr_talkTime) AS "Total Talk Time (sec)" ,CASE WHEN SUM(CAST(intr_answered AS INTEGER) - CAST(intr_rejected AS INTEGER)) > 0 THEN TO_CHAR(ROUND((CAST(SUM(CASE WHEN intr_answered = 1 AND intr_rejected = 0 THEN intr_talkTime ELSE 0 END) AS DECIMAL) / SUM(CASE WHEN intr_answered = 1 AND intr_rejected = 0 THEN 1 ELSE 0 END)), 2)) ELSE 'N/A' END AS "Average Talk Time (sec)" ,SUM(CAST(intr_transferredIn AS INTEGER)) AS "Agent Calls Transferred" ,AVG(S.staffedCount) AS "Agents Staffed" ,AVG(AV.availableCount) AS "Agents Available" FROM ECSInteractionFact I LEFT OUTER JOIN (SELECT DISTINCT iseg_interactionId, intr_queueTime AS abandonedQueueTime, iseg_startDateLocal FROM ECSInteractionFact INNER JOIN ECSInteractionSegmentFact ON intr_interactionId = iseg_interactionId WHERE intr_handledByAgent = 0 AND iseg_interactionStateName IS NOT NULL AND iseg_interactionStateName = 'Abandoned' AND intr_queued = 1 AND intr_inbound = 1 AND intr_interactionMediaName = 'Telephony') A ON intr_interactionId = A.iseg_interactionId AND I.intr_startDateLocal = A.iseg_startDateLocal LEFT OUTER JOIN (SELECT DISTINCT iseg_interactionId, SUM(DATEDIFF( second, iseg_startTime, iseg_endTime )) AS selectDuration, iseg_startDateLocal FROM ECSInteractionFact INNER JOIN ECSInteractionSegmentFact ON intr_interactionId = iseg_interactionId WHERE iseg_interactionStateName IS NOT NULL AND iseg_interactionStateName = 'AgentSelecting' AND intr_queued = 1 AND intr_inbound = 1 AND intr_interactionMediaName = 'Telephony' GROUP BY iseg_interactionId, iseg_startDateLocal) R ON intr_interactionId = R.iseg_interactionId AND I.intr_startDateLocal = R.iseg_startDateLocal LEFT OUTER JOIN TimeOfDayDimension TOD ON intr_startTimeLocal = td_timeOfDay LEFT OUTER JOIN DateDimension DT ON intr_startDateLocal = dd_date LEFT OUTER JOIN (SELECT alif_startDateLocal, td_halfHourOfDay, COUNT(DISTINCT alif_agentId) staffedCount FROM TimeOfDayDimension LEFT OUTER JOIN ECSAgentLifeFact ON alif_startTimeLocal <= td_lastSecondOfHalfHour AND alif_endTimeLocal >= td_firstSecondOfHalfHour WHERE alif_agentStatusName <> 'Offline' GROUP BY alif_startDateLocal, td_halfHourOfDay) S ON S.td_halfHourOfDay = TOD.td_halfHourOfDay AND I.intr_startDateLocal = S.alif_startDateLocal LEFT OUTER JOIN (SELECT alif_startDateLocal, td_halfHourOfDay, COUNT(DISTINCT alif_agentId) availableCount FROM TimeOfDayDimension LEFT OUTER JOIN ECSAgentLifeFact ON alif_startTimeLocal <= td_lastSecondOfHalfHour AND alif_endTimeLocal >= td_firstSecondOfHalfHour WHERE alif_agentStatusName IN ('Ready', 'Busy') GROUP BY alif_startDateLocal, td_halfHourOfDay) AV ON AV.td_halfHourOfDay = TOD.td_halfHourOfDay AND I.intr_startDateLocal = AV.alif_startDateLocal WHERE intr_queued = 1 AND intr_inbound = 1 AND intr_interactionMediaName = 'Telephony' AND intr_startDateLocal >= 20220302 AND intr_startDateLocal <= 20220303 GROUP BY dd_dateAsDate, TOD.td_halfHourLabel ORDER BY dd_dateAsDate, TOD.td_halfHourLabel
- No labels