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
*/