The example below queries the BroadsoftCCAgentCallFact for agent details by call center agents for a specified month.
-- DBS agent call details
SELECT TOP 1000 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 BroadsoftCCAgentCallFact LEFT OUTER JOIN BroadsoftUserDimension ON acf_agentUserFk = bud_id
WHERE acf_callStartedDateUTC >= 20220901 AND acf_callStartedDateUTC < 20221001;
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 BroadsoftCCQueueCallFact
WHERE qcf_callCreationDateUTC >= 20220801 AND qcf_callCreationDateUTC < 20220901
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 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 BroadsoftCCQueueCallFact
WHERE qcf_callCreationDateUTC >= 20220801 AND qcf_callCreationDateUTC < 20220901
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
*/
-- DBS queue call counters by hour & status
SELECT qcf_callCreationDateUTC "Date UTC",
FLOOR(qcf_callCreationTimeUTC / 10000) "Hour",
COALESCE( qcf_status, qcf_deflectedCallReason ) "Status",
COUNT(*) "Call Count",
SUM( qcf_callOfferedCount ) "Offered Count",
SUM( qcf_callBouncedCount ) "Bounced Count"
FROM BroadsoftCCQueueCallFact
WHERE qcf_callCreationDateUTC >= 20230301 AND qcf_callCreationDateUTC < 20230302
GROUP BY qcf_callCreationDateUTC, FLOOR(qcf_callCreationTimeUTC / 10000), COALESCE( qcf_status, qcf_deflectedCallReason )
ORDER BY "Date UTC", "Hour", "Status";
-- CAST((qcf_callCreationTimeUTC / 10000) AS INTEGER)
/*
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
BroadsoftCCQueueCallFact qcf
INNER JOIN DateDimension dt ON dt.dd_date = qcf.qcf_callReleasedDateLocal
left outer join BroadsoftUserDimension ud on ud.bud_id = qcf.qcf_agentUserFk
left outer join BroadsoftCallCenterDimension ccd on ccd.bccd_id = qcf.qcf_callCenterFk
left outer join BroadsoftUserDimension QU on QU.bud_broadsoftUserId = ccd.bccd_userId
left outer join LocationDimension AL on ud.bud_locationId = AL.lcd_location
left outer join LocationDimension QL on QU.bud_locationId = QL.lcd_location
WHERE qcf.qcf_callCreationDateLocal >= 20230301 AND qcf.qcf_callCreationDateLocal < 20230302
/*
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
*/