Page History

2.02 Report: Agent Time Allocation Performance

Download SQL file: 2.02-AgentTimeAllocationPerformanceAgentTimeAllocationPerformanceSnow.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.

...

Code Block

language	sql
title	2.02 Agent Time Allocation Performance
collapse	true

/*
 * 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
DECLARELET @StartDateLocal AS INT = 20220302StartDateLocal number(38, 2) := 20230302; -- Inclusive
DECLARELET @EndDateLocal AS INT = 20220303EndDateLocal number(38, 2) := 20230303;   -- Exclusive

LET res RESULTSET := (SELECT dd_dateAsDate ["Date]",
    eud_ecsUserDisplayName ["Agent Name]",
	SUM(DATEDIFF( sssecond, alif_startTimestampLocal, alif_endTimestampLocal )) ["Total Login Time]",
	SUM(CASE WHEN alif_agentStatusName NOT IN ('Ready', 'Offered') THEN DATEDIFF( sssecond, alif_startTimestampLocal, alif_endTimestampLocal ) ELSE 0 END) ["Total Not Ready Time]",
	SUM(CASE WHEN alif_agentStatusName IN ('Ready', 'Offered') THEN DATEDIFF( sssecond, 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( sssecond, alif_startTimestampLocal, alif_endTimestampLocal ) ELSE 0 END) ["Total Busy Time]",
	SUM(CASE WHEN alif_agentStatusName = 'No Answer' THEN DATEDIFF( sssecond, alif_startTimestampLocal, alif_endTimestampLocal ) ELSE 0 END) ["Total No Answer Time]",
	SUM(CASE WHEN alif_agentStatusName = 'Break' THEN DATEDIFF( sssecond, alif_startTimestampLocal, alif_endTimestampLocal ) ELSE 0 END) ["Total Break Time]",
	SUM(CASE WHEN alif_agentStatusName = 'Handling - Outgoing Interactions' THEN DATEDIFF( sssecond, alif_startTimestampLocal, alif_endTimestampLocal ) ELSE 0 END) ["Total Handling Outgoing Interactions]",
	SUM(CASE WHEN alif_agentStatusName = 'Backoffice - Administrative' THEN DATEDIFF( sssecond, alif_startTimestampLocal, alif_endTimestampLocal ) ELSE 0 END) ["Total Backoffice Time]",
	FORMATTO_VARCHAR(CASE WHEN SUM(DATEDIFF( sssecond, alif_startTimestampLocal, alif_endTimestampLocal )) > 0
		THEN SUM(CASE WHEN alif_agentStatusName LIKEILIKE 'WaitingFor%' OR alif_agentStatusName IN ('Busy', 'Dialing') THEN DATEDIFF( sssecond, alif_startTimestampLocal, alif_endTimestampLocal ) ELSE 0 END) / CAST( SUM(DATEDIFF( sssecond, alif_startTimestampLocal, alif_endTimestampLocal )) AS DECIMAL)
		ELSE 0 END * 100,'999') || 'P0 %') ["Busy To Login Time Ratio]"
FROM ECSAgentLifeFact AL
	LEFT OUTER JOIN DateDimension DT WITH(NOLOCK) ON alif_startDateLocal = dd_date
	LEFT OUTER JOIN ECSUserDimension U WITH(NOLOCK) 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_agentLifeTypeIdagentLifeTypeName = 1'shift' AND AL.alif_startDateLocal >= @StartDateLocal:StartDateLocal AND AL.alif_startDateLocal < @EndDateLocal: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
DECLARE
EXECUTE @StartDateLocalDetail AS INT = 20220302; -- Inclusive
DECLARE @EndDateLocalDetail AS INT = 20220303IMMEDIATE $$
-- 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:StartDateLocalDetail AND iseg_startDateLocal < @EndDateLocalDetail: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:StartDateLocalDetail AND iseg_startDateLocal < @EndDateLocalDetail: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 WITH(NOLOCK) ON iseg_startDateLocal = dd_date
	LEFT OUTER JOIN ECSUserDimension U WITH(NOLOCK) 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-AgentInteractionSummaryAgentInteractionSummarySnow.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.

Code Block

language	sql
title	2.04 Agent Interaction Summary
collapse	true

/*
 * 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
DECLAREEXECUTE @StartDateLocal INT IMMEDIATE $$
-- Snowflake Scripting code

BEGIN

LET StartDateLocal number(38, 2) := 20220302; -- Inclusive
DECLARE @EndDateLocal INT LET EndDateLocal number(38, 2) := 20220303;   -- Exclusive

LET res RESULTSET := (SELECT eud_ecsUserDisplayName ["Agent Name]",
    dd_dateAsDate ["Date]",
	ISNULLIFNULL(SUM(CASE WHEN iseg_interactionStateName = 'Handling' THEN 1 ELSE 0 END), 0) ["Interactions Handled]",
	ISNULLIFNULL(SUM(CASE WHEN iseg_interactionStateName = 'Missed' THEN 1 ELSE 0 END), 0) ["Interactions Missed]",
	ISNULLIFNULL(SUM(CASE WHEN iseg_interactionStateName = 'Rejected' THEN 1 ELSE 0 END), 0) ["Interactions Rejected]",
	ISNULLIFNULL(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]",
	ISNULLIFNULL(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]",
	FORMAT(ISNULLTO_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), 'P2999.99') [ || ' %' "Transfer %]",
	ISNULLIFNULL(SUM(CASE WHEN iseg_interactionStateName = 'Handling' AND iseg_interactionCreationTypeName = 'Incoming' THEN 1 ELSE 0 END), 0) ["Inbound]",
	ISNULLIFNULL(SUM(CASE WHEN iseg_interactionStateName = 'Handling' AND iseg_interactionCreationTypeName IN ('OutgoingExternal', 'Campaign') THEN 1 ELSE 0 END), 0) ["Outbound]",
	ISNULLIFNULL(SUM(CASE WHEN iseg_interactionStateName = 'Handling' AND iseg_interactionCreationTypeName = 'Callback' THEN 1 ELSE 0 END), 0) ["Callback]",
	ISNULLIFNULL(SUM(CASE WHEN iseg_interactionStateName = 'Handling' AND iseg_interactionMediaName = 'Telephony' THEN 1 ELSE 0 END), 0) ["Voice]",
	ISNULLIFNULL(SUM(CASE WHEN iseg_interactionStateName = 'Handling' AND iseg_interactionMediaName = 'Chat' THEN 1 ELSE 0 END), 0) ["Chat]",
	ISNULLIFNULL(SUM(CASE WHEN iseg_interactionStateName = 'Handling' AND iseg_interactionMediaName = 'Email' THEN 1 ELSE 0 END), 0) ["Email]",
	ISNULLIFNULL(SUM(CASE WHEN iseg_interactionStateName = 'Handling' AND iseg_interactionMediaName = 'SMS' THEN 1 ELSE 0 END), 0) ["SMS]",
	ISNULLIFNULL(AVG(AL.readyIdleTime), 0) ["Ready Idle]",
	ISNULLIFNULL(AVG(AL.breakTime), 0) ["Break]",
	ISNULLIFNULL(AVG(AL.busyTime), 0) ["Busy]",
	ISNULLIFNULL(AVG(DATEDIFF(sssecond, AL.staffedStart, AL.staffedEnd)), 0) ["Staffed]",
	ISNULLIFNULL(AVG(AL.noAnswerTime), 0) ["No Answer]",
	ISNULLIFNULL(AVG(AL.backOfficeTime), 0) ["BackOffice Idle]",
	ISNULLIFNULL(AVG(AL.outgoingIdleTime), 0) ["Handle Outgoing Idle]",
	ISNULLIFNULL(SUM(CASE WHEN I.iseg_InteractionMediaName = 'Telephony' THEN I.talkTime ELSE 0 END), 0) ["Talk Voice]",
	ISNULLIFNULL(SUM(CASE WHEN I.iseg_InteractionMediaName = 'Telephony' AND I.iseg_interactionStateName = 'Handling' THEN I.holdTime ELSE 0 END), 0) ["Hold Voice]",
	ISNULLIFNULL(SUM(CASE WHEN I.iseg_InteractionMediaName = 'Telephony' AND I.iseg_interactionStateName = 'Wrap Up' THEN I.wrapUpTime ELSE 0 END), 0) ["Wrap Up Voice]",
	ISNULLIFNULL(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]",
	ISNULLIFNULL(SUM(I.offeredTime) / NULLIF(SUM(I.offeredCount), 0), 0) ["Avg. Offer Time]",
	ISNULLIFNULL((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]",
	ISNULLIFNULL(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]",
	ISNULLIFNULL(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]",
	ISNULLIFNULL((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]",
	ISNULLIFNULL(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]",
	ISNULLIFNULL(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]",
	ISNULLIFNULL((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]",
	ISNULLIFNULL(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]",
	ISNULLIFNULL(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]",
	ISNULLIFNULL((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]",
	ISNULLIFNULL(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]",
	FORMAT(ISNULL
    TO_VARCHAR((IFNULL(AVG(AL.busyTime) / CAST(NULLIF(AVG(AL.readyIdleTime) + AVG(AL.busyTime), 0) AS DECIMAL), 0) * 100), 'P2999.99') [|| ' %' "Occupancy %]",
	FORMAT(ISNULL
    TO_VARCHAR((IFNULL((AVG(AL.busyTime) + AVG(AL.readyIdleTime)) / CAST(NULLIF(AVG(DATEDIFF(sssecond, AL.staffedStart, AL.staffedEnd)), 0) AS DECIMAL), 0) * 100), 'P2999.99') [|| ' %' "Utilization %]",
	FORMAT(ISNULL
    TO_VARCHAR((IFNULL(AVG(AL.readyIdleTime) / CAST(NULLIF(AVG(DATEDIFF(sssecond, AL.staffedStart, AL.staffedEnd)), 0) AS DECIMAL), 0) * 100), 'P2999.99') [|| ' %' "Ready %]",
	FORMAT(ISNULL

    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), 'P2999.99') [ || ' %' "Talk Time % (Voice)]",
	FORMAT(ISNULL
    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), 'P2999.99') [ || ' %' "Hold Time % (Voice)]",
	FORMAT(ISNULL

    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), 'P2999.99') [ || ' %'"Wrap Up % (Voice)]",
	FORMAT(ISNULL
    TO_VARCHAR((IFNULL((AVG(AL.breakTime) + AVG(AL.noAnswerTime)) / CAST(NULLIF(AVG(DATEDIFF(sssecond, AL.staffedStart, AL.staffedEnd)), 0) AS DECIMAL), 0) * 100), 'P2999.99') [ || ' %' "Break Time %]"

FROM (SELECT DISTINCT alif_agentId, alif_startDateLocal, SUM(CASE WHEN alif_agentStatusName IN ('Ready', 'Offered') THEN DATEDIFF( sssecond, alif_startTimestampLocal, alif_endTimestampLocal ) ELSE 0 END) readyIdleTime,
			SUM(CASE WHEN alif_agentStatusName IN ('Break') THEN DATEDIFF( sssecond, 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( sssecond, alif_startTimestampLocal, alif_endTimestampLocal ) ELSE 0 END) busyTime,
			SUM(CASE WHEN alif_agentStatusName = 'No Answer' THEN DATEDIFF( sssecond, alif_startTimestampLocal, alif_endTimestampLocal ) ELSE 0 END) noAnswerTime,
			SUM(CASE WHEN alif_agentStatusName = 'Backoffice - Administrative' THEN DATEDIFF( sssecond, alif_startTimestampLocal, alif_endTimestampLocal ) ELSE 0 END) backOfficeTime,
			SUM(CASE WHEN alif_agentStatusName = 'Handling - Outgoing Interactions' THEN DATEDIFF( sssecond, 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:StartDateLocal AND alif_startDateLocal < @EndDateLocal:EndDateLocal
		GROUP BY alif_agentId, alif_startDateLocal) AL
	LEFT OUTER JOIN DateDimension DT WITH(NOLOCK) ON alif_startDateLocal = dd_date
	LEFT OUTER JOIN ECSUserDimension U WITH(NOLOCK) 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:StartDateLocal AND iseg_interactionStartDateLocal < @EndDateLocal: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
DECLARE @StartDateLocalDetail AS INT EXECUTE IMMEDIATE $$
-- Snowflake Scripting code

BEGIN

LET StartDateLocalDetail number(38, 2) := 20220302; -- Inclusive
DECLARELET @EndDateLocalDetail AS INT EndDateLocalDetail number(38, 2) := 20220303;   -- Exclusive
DECLARELET @SecondsInPeriod AS INT 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]",
	ISNULLIFNULL(SUM(CASE WHEN iseg_interactionStateName = 'Handling' THEN 1 ELSE 0 END), 0) ["Interactions Handled]",
	ISNULLIFNULL(SUM(CASE WHEN iseg_interactionStateName = 'Missed' THEN 1 ELSE 0 END), 0) ["Interactions Missed]",
	ISNULLIFNULL(SUM(CASE WHEN iseg_interactionStateName = 'Rejected' THEN 1 ELSE 0 END), 0) ["Interactions Rejected]",
	ISNULLIFNULL(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]",
	ISNULLIFNULL(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]",
	FORMAT(ISNULLTO_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), 'P2999.99') [ || ' %' "Transfer %]",
	ISNULLIFNULL(SUM(CASE WHEN iseg_interactionStateName = 'Handling' AND iseg_interactionCreationTypeName = 'Incoming' THEN 1 ELSE 0 END), 0) ["Inbound]",
	ISNULLIFNULL(SUM(CASE WHEN iseg_interactionStateName = 'Handling' AND iseg_interactionCreationTypeName IN ('OutgoingExternal', 'Campaign') THEN 1 ELSE 0 END), 0) ["Outbound]",
	ISNULLIFNULL(SUM(CASE WHEN iseg_interactionStateName = 'Handling' AND iseg_interactionCreationTypeName = 'Callback' THEN 1 ELSE 0 END), 0) ["Callback]",
	ISNULLIFNULL(SUM(CASE WHEN iseg_interactionStateName = 'Handling' AND iseg_interactionMediaName = 'Telephony' THEN 1 ELSE 0 END), 0) ["Voice]",
	ISNULLIFNULL(SUM(CASE WHEN iseg_interactionStateName = 'Handling' AND iseg_interactionMediaName = 'Chat' THEN 1 ELSE 0 END), 0) ["Chat]",
	ISNULLIFNULL(SUM(CASE WHEN iseg_interactionStateName = 'Handling' AND iseg_interactionMediaName = 'Email' THEN 1 ELSE 0 END), 0) ["Email]",
	ISNULLIFNULL(SUM(CASE WHEN iseg_interactionStateName = 'Handling' AND iseg_interactionMediaName = 'SMS' THEN 1 ELSE 0 END), 0) ["SMS]",
	ISNULLIFNULL(ROUND(AVG(AL.readyIdleTime), 0), [2) "Ready Idle]",
	ISNULLIFNULL(AVG(AL.breakTime), 0) ["Break]",
	ISNULLIFNULL(AVG(AL.busyTime), 0) ["Busy]",
	ISNULLIFNULL(LEAST(@SecondsInPeriod:SecondsInPeriod, AVG(DATEDIFF(sssecond, AL.staffedStart, AL.staffedEnd))), 0) ["Staffed]",
	ISNULLIFNULL(AVG(AL.noAnswerTime), 0) ["No Answer]",
	ISNULLIFNULL(AVG(AL.backOfficeTime), 0) ["BackOffice Idle]",
	ISNULLIFNULL(AVG(AL.outgoingIdleTime), 0) ["Handle Outgoing Idle]",
	ISNULLIFNULL(SUM(CASE WHEN I.iseg_InteractionMediaName = 'Telephony' THEN I.talkTime ELSE 0 END), 0) ["Talk Voice]",
	ISNULLIFNULL(SUM(CASE WHEN I.iseg_InteractionMediaName = 'Telephony' AND I.iseg_interactionStateName = 'Handling' THEN I.holdTime ELSE 0 END), 0) ["Hold Voice]",
	ISNULLIFNULL(SUM(CASE WHEN I.iseg_InteractionMediaName = 'Telephony' AND I.iseg_interactionStateName = 'Wrap Up' THEN I.wrapUpTime ELSE 0 END), 0) ["Wrap Up Voice]",
	ISNULLIFNULL(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]",
	ISNULL

    IFNULL(ROUND(SUM(I.offeredTime) / NULLIF(SUM(I.offeredCount), 0), 0), 2) ["Avg. Offer Time]",
	ISNULL

    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]",
	ISNULL

    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]",
	ISNULL
    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]",
	ISNULL

    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]",
	ISNULL

    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]",
	ISNULL

    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]",
	ISNULL
    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]",
	ISNULL
    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]",
	ISNULL
    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]",
	ISNULL

    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]",
	ISNULL

    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]",
	FORMAT(ISNULL
    TO_VARCHAR((IFNULL(AVG(AL.busyTime) / CAST(NULLIF(AVG(AL.readyIdleTime) + AVG(AL.busyTime), 0) AS DECIMAL), 0)* 100), 'P2999.99') [ || ' %' "Occupancy %]",
	FORMAT(ISNULL

    TO_VARCHAR((IFNULL((AVG(AL.busyTime) + AVG(AL.readyIdleTime)) / CAST(NULLIF(AVG(DATEDIFF(sssecond, AL.staffedStart, AL.staffedEnd)), 0) AS DECIMAL), 0)* 100), 'P2999.99') [|| ' %' "Utilization %]",
	FORMAT(ISNULL

    TO_VARCHAR((IFNULL(AVG(AL.readyIdleTime) / CAST(NULLIF(AVG(DATEDIFF(sssecond, AL.staffedStart, AL.staffedEnd)), 0) AS DECIMAL), 0)* 100), 'P2999.99') [ || ' %' "Ready %]",
	FORMAT(ISNULL

    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), 'P2999.99') [|| ' %' "Talk Time % (Voice)]",
	FORMAT(ISNULL
    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), 00)* 100), 'P2999.99') [ || ' %' "Hold Time % (Voice)]",
	FORMAT(ISNULL

    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), 'P2999.99') [ || ' %' "Wrap Up % (Voice)]",
	FORMAT(ISNULL

    TO_VARCHAR((IFNULL((AVG(AL.breakTime) + AVG(AL.noAnswerTime)) / CAST(NULLIF(AVG(DATEDIFF(sssecond, AL.staffedStart, AL.staffedEnd)), 0) AS DECIMAL), 0)* 100), 'P2999.99') ["Break Time %]"
FROM (SELECT DISTINCT td_hourLabel, alif_agentId, alif_startDateLocal, LEAST(@SecondsInPeriod:SecondsInPeriod, SUM(readyIdleTime)) AS readyIdleTime, LEAST(@SecondsInPeriod:SecondsInPeriod, SUM(breakTime)) AS breakTime,
			LEAST(@SecondsInPeriod:SecondsInPeriod, SUM(busyTime)) AS busyTime, LEAST(@SecondsInPeriod:SecondsInPeriod, SUM(noAnswerTime)) AS noAnswerTime, LEAST(@SecondsInPeriod:SecondsInPeriod, SUM(backOfficeTime)) AS backOfficeTime,
			LEAST(@SecondsInPeriod: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( sssecond, alif_startTimestampLocal, alif_endTimestampLocal ) ELSE 0 END readyIdleTime,
				CASE WHEN alif_agentStatusName IN ('Break') THEN DATEDIFF( sssecond, 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( sssecond, alif_startTimestampLocal, alif_endTimestampLocal ) ELSE 0 END busyTime,
				CASE WHEN alif_agentStatusName = 'No Answer' THEN DATEDIFF( sssecond, alif_startTimestampLocal, alif_endTimestampLocal ) ELSE 0 END noAnswerTime,
				CASE WHEN alif_agentStatusName = 'Backoffice - Administrative' THEN DATEDIFF( sssecond, alif_startTimestampLocal, alif_endTimestampLocal ) ELSE 0 END backOfficeTime,
				CASE WHEN alif_agentStatusName = 'Handling - Outgoing Interactions' THEN DATEDIFF( sssecond, 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:StartDateLocalDetail AND alif_startDateLocal < @EndDateLocalDetail:EndDateLocalDetail) IAL
		GROUP BY alif_startDateLocal, alif_agentId, td_hourLabel) AL
	LEFT OUTER JOIN DateDimension DT WITH(NOLOCK) ON alif_startDateLocal = dd_date
	LEFT OUTER JOIN ECSUserDimension U WITH(NOLOCK)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:SecondsInPeriod, SUM(talkTime)) talkTime, LEAST(@SecondsInPeriod:SecondsInPeriod, SUM(holdTime)) holdTime, LEAST(@SecondsInPeriod:SecondsInPeriod, SUM(wrapUpTime)) wrapUpTime,
			LEAST(@SecondsInPeriod:SecondsInPeriod, SUM(offeredTime)) offeredTime, LEAST(@SecondsInPeriod: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:StartDateLocalDetail AND iseg_interactionStartDateLocal < @EndDateLocalDetail: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-QueueIntervalSummaryReportQueueIntervalSummaryReportSnow.sql

The example below replicates the data found in the standard 5.05 ECS report.

Code Block

language	sql
title	5.05 Queue Interval Summary Report
collapse	true

/*
 * 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 FORMATTO_VARCHAR((CAST(COUNT(A.abandonedQueueTime) AS DECIMAL) / COUNT(*)) * 100, 'P2999') || ' %' ELSE 'N/A' END AS ["% Total Abandoned Calls]"
,CASE WHEN COUNT(*) > 0 THEN FORMATTO_VARCHAR((CAST(SUM(CASE WHEN A.abandonedQueueTime > 30 THEN 1 ELSE 0 END) AS DECIMAL) / COUNT(*)) * 100, 'P2999') || ' %' 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
		STRTO_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), 12), 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
		STRTO_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)), 12, 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 [dbo].[ECSInteractionFact] I
	LEFT OUTER JOIN (SELECT DISTINCT iseg_interactionId, intr_queueTime AS abandonedQueueTime, iseg_startDateLocal FROM ECSInteractionFact INNER JOIN ECSInteractionSegmentFact WITH(NOLOCK) 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( sssecond, iseg_startTime, iseg_endTime )) AS selectDuration, iseg_startDateLocal FROM ECSInteractionFact INNER JOIN ECSInteractionSegmentFact WITH(NOLOCK) 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 WITH(NOLOCK) ON intr_startTimeLocal = td_timeOfDay
	LEFT OUTER JOIN DateDimension DT WITH(NOLOCK) 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

Manage page tree

TEST

Are you sure?

Are you sure?

Page tree

Versions Compared

Old Version 1

New Version Current

Key

Download SQL file: 2.02-AgentTimeAllocationPerformanceAgentTimeAllocationPerformanceSnow.sql

Download SQL file: 2.04-AgentInteractionSummaryAgentInteractionSummarySnow.sql

Download SQL file: 5.05-QueueIntervalSummaryReportQueueIntervalSummaryReportSnow.sql