The example below queries the BroadsoftCCAgentCallFact for agent details by call center agents for a specified month.
-- DBS agent call details SELECT [bud_broadsoftUserDisplayName], [acf_creationTimestamp], [acf_callReleasedTimestamp], [acf_callType], [acf_agentSkill], [acf_remoteNumber], [acf_numberCalled], [acf_callStartedTimestamp], [acf_callTalkDuration], [acf_callHeldDuration], [acf_wrapUpDuration], [acf_redirectNumber] ,[acf_redirectLocation] FROM [dbo].[BroadsoftCCAgentCallFact] LEFT OUTER JOIN [dbo].[BroadsoftUserDimension] ON [acf_agentUserFk] = [bud_id] WHERE [acf_callStartedDateLocal] >= 20210901 AND [acf_callStartedDateLocal] < 20211001; |
The example below queries the BroadsoftCCQueueCallFact for queue statistics by date and call status.
-- DBS queue call counters by day & status SELECT [qcf_callCreationDateUTC] [Date UTC], COALESCE( [qcf_status], [qcf_deflectedCallReason] ) [Status], COUNT(*) [Call Count], SUM( [qcf_callOfferedCount] ) [Offered Count], SUM( [qcf_callBouncedCount] ) [Bounced Count] FROM [dbo].[BroadsoftCCQueueCallFact] WHERE [qcf_callCreationDateUTC] >= 20210801 AND [qcf_callCreationDateUTC] < 20210901 GROUP BY [qcf_callCreationDateUTC], COALESCE( [qcf_status], [qcf_deflectedCallReason] ) ORDER BY [Date UTC], [Status]; /* Date UTC Status Call Count Offered Count Bounced Count 20210826 CALLABANDONED 1284 225 141 20210826 CALLABANDONEDENTRANCE 52 20 0 20210826 CALLANSWERED 8757 10452 1705 20210826 CALLOVERFLOWEDBYTIME 8 5 2 20210826 CALLSTRANDED 8 1 1 20210826 CALLSTRANDEDUNAVAILABLE 589 51 51 20210826 CALLTRANSFERRED 17 4 1 20210826 FORCEDFORWARDAPPLIED 37 0 0 20210826 NIGHTSERVICEAPPLIED 407 0 0 20210827 CALLABANDONED 1404 210 132 */ |
The example below queries the BroadsoftCCQueueCallFact for queue statistics by date, hour-of-day and call status.
-- DBS queue call counters by hour & status SELECT [qcf_callCreationDateUTC] [Date UTC], [qcf_callCreationTimeUTC] / 10000 [Hour], COALESCE( [qcf_status], [qcf_deflectedCallReason] ) [Status], COUNT(*) [Call Count], SUM( [qcf_callOfferedCount] ) [Offered Count], SUM( [qcf_callBouncedCount] ) [Bounced Count] FROM [dbo].[BroadsoftCCQueueCallFact] WHERE [qcf_callCreationDateUTC] >= 20210801 AND [qcf_callCreationDateUTC] < 20210802 GROUP BY [qcf_callCreationDateUTC], [qcf_callCreationTimeUTC] / 10000, COALESCE( [qcf_status], [qcf_deflectedCallReason] ) ORDER BY [Date UTC], [Hour], [Status]; /* Date UTC Hour Status Call Count Offered Count Bounced Count 20210901 0 CALLANSWERED 37 40 3 20210901 0 FORCEDFORWARDAPPLIED 3 0 0 20210901 0 NIGHTSERVICEAPPLIED 186 0 0 20210901 1 CALLABANDONED 4 2 2 20210901 1 CALLANSWERED 24 27 5 20210901 1 FORCEDFORWARDAPPLIED 1 0 0 20210901 1 NIGHTSERVICEAPPLIED 110 0 0 20210901 2 CALLANSWERED 16 16 0 20210901 2 NIGHTSERVICEAPPLIED 60 0 0 20210901 3 NIGHTSERVICEAPPLIED 45 0 0 20210901 4 NIGHTSERVICEAPPLIED 16 0 0 20210901 5 NIGHTSERVICEAPPLIED 15 0 0 20210901 6 NIGHTSERVICEAPPLIED 3 0 0 */ |
The example below queries the BroadsoftCCQueueCallFact for queue call details.
-- DBS Show queue and agent locations for queued calls SELECT DISTINCT QL.lcd_location AS [Queue Location], CASE WHEN QL.lcd_location IS NOT NULL THEN CONCAT( QL.[lcd_city], ', ', QL.[lcd_stateProvince] ) ELSE NULL END AS [Queue Address], AL.lcd_location AS [Agent Location], CASE WHEN AL.lcd_location IS NOT NULL THEN CONCAT( AL.[lcd_city], ', ', AL.[lcd_stateProvince] ) ELSE NULL END AS [Agent Address], dt.dd_dateAsDate,ccd.bccd_name,ccd.bccd_userId, qcf.qcf_status, qcf.qcf_deflectedCallReason FROM dbo.BroadsoftCCQueueCallFact qcf with (nolock) INNER JOIN dbo.DateDimension dt WITH (NOLOCK) ON dt.dd_date = qcf.qcf_callReleasedDateLocal left outer join dbo.BroadsoftUserDimension ud with (nolock) on ud.bud_id = qcf.qcf_agentUserFk left outer join dbo.BroadsoftCallCenterDimension ccd with (nolock) on ccd.bccd_id = qcf.qcf_callCenterFk left outer join dbo.BroadsoftUserDimension QU with (nolock) on QU.bud_broadsoftUserId = ccd.bccd_userId left outer join dbo.LocationDimension AL with (nolock) on ud.bud_locationId = AL.lcd_location left outer join dbo.LocationDimension QL with (nolock) on QU.bud_locationId = QL.lcd_location WHERE qcf.qcf_callCreationDateLocal >= 20211008 AND qcf.qcf_callCreationDateLocal < 20211009; /* Queue Location Queue Address Agent Location Agent Address dd_dateAsDate bccd_name bccd_userId qcf_status qcf_deflectedCallReason 0001000XXX EXTON, PA NULL NULL 2022-01-08 Alpha xxxxx cc-0001000XXX-Alphaxxxxx NULL NIGHTSERVICEAPPLIED 0001000XXX EXTON, PA NULL NULL 2022-01-08 yyy App Queue cc-0001000XXX-AppCC NULL NIGHTSERVICEAPPLIED 0001005437 KING OF PRUSSIA, PA NULL NULL 2022-01-08 Flex Support cc-0001005437-999 CALLABANDONEDENTRANCE NULL */ |