The example below queries the data warehouse for data surrounding hunt group calls including how many were answered, abandoned and transferred to voicemail as well as duration of the ring times and calls
It is selected based on a specific date range and groups the data by location and user
Hunt Group Statistics
SELECT
[bf_callStartDateLocal] AS [ Date ],
CONCAT([lcd_stateProvince], ', ' , [lcd_city], ' - ' , [lcd_streetAddress]) AS [Location],
[bud_broadsoftUserDisplayName] AS [Hunt Group ],
SUM ( CASE [bf_direction] WHEN 'Terminating' THEN 1 ELSE 0 END ) AS [Inbound Calls],
SUM ( CASE [bf_direction] WHEN 'Terminating' THEN 1 ELSE 0 END ) -
SUM ( CASE WHEN [bf_direction] = 'Originating' AND [bf_answerIndicator] = 'Y' THEN 1 ELSE 0 END ) AS [Abandoned Calls],
SUM ( CASE WHEN [bf_direction] = 'Originating' AND [bf_answerIndicator] = 'Y' THEN 1 ELSE 0 END ) AS [Routed Calls],
SUM ( CASE WHEN [bf_direction] = 'Originating' AND [bf_answerIndicator] = 'Y' AND bf_calledNumber <> '5000' THEN 1 ELSE 0 END ) AS [Handled Calls],
CAST ( SUM ( CASE WHEN [bf_direction] = 'Originating' AND [bf_answerIndicator] = 'Y' AND bf_calledNumber <> '5000' THEN [bf_billDuration] ELSE 0 END ) / 60.0 AS DECIMAL (8,2)) AS [Talk Duration],
SUM ( CASE WHEN [bf_direction] = 'Originating' AND [bf_answerIndicator] = 'Y' AND bf_calledNumber = '5000' THEN 1 ELSE 0 END ) AS [VM Overflow Calls],
CAST ( SUM ( CASE WHEN [bf_direction] = 'Originating' AND [bf_answerIndicator] = 'Y' AND bf_calledNumber <> '5000' THEN [bf_ringDuration] ELSE 0 END ) / 60.0 AS DECIMAL (8,2)) AS [Ring Duration],
FROM [BroadsoftCDRFact] LEFT OUTER JOIN [BroadsoftUserDimension] ON [bf_userFK] = [bud_id]
LEFT OUTER JOIN [LocationDimension] ON SUBSTRING ([bf_group], 4, 10) = [lcd_location]
WHERE [bf_callStartDateLocal] >= 20210414 AND [bf_callStartDateLocal] < 20210415 AND
[bf_userId] LIKE 'hg-%'
GROUP BY [bf_callStartDateLocal], CONCAT([lcd_stateProvince], ', ' , [lcd_city], ' - ' , [lcd_streetAddress]), [bud_broadsoftUserDisplayName];
|