Page History

SELECT
	-- Date
	[bf_callStartDateLocal] AS [Date],

	-- Location
	CONCAT([lcd_stateProvince], ', ', [lcd_city], ' - ', [lcd_streetAddress]) AS [Location],

	-- Hunt Group
	[bud_broadsoftUserDisplayName] AS [Hunt Group],

	-- Inbound calls to Hunt group
	SUM( CASE [bf_direction] WHEN 'Terminating' THEN 1 ELSE 0 END ) AS [Inbound Calls],

	-- Calls abandoned before answer or VM
	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],

	-- Calls routed to agents
	SUM( CASE WHEN [bf_direction] = 'Originating' AND [bf_answerIndicator] = 'Y' THEN 1 ELSE 0 END ) AS [Routed Calls],

	-- Handled calls (calls answered by a person)
	SUM( CASE WHEN [bf_direction] = 'Originating' AND [bf_answerIndicator] = 'Y' AND bf_calledNumber <> '5000' THEN 1 ELSE 0 END ) AS [Handled Calls],

	-- Talk duration of handled calls in minutes
	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],

	-- Call overflowed to VM
	SUM( CASE WHEN [bf_direction] = 'Originating' AND [bf_answerIndicator] = 'Y' AND bf_calledNumber = '5000' THEN 1 ELSE 0 END ) AS [VM Overflow Calls],

	-- Ring duration in minutes
	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];

SELECT [bf_callStartDateLocal] / 100 AS month, [bf_calledNumber] AS DNIS, COUNT(*) AS callCount, CAST(SUM(bf_billDuration) / 60.0 AS DECIMAL(8,2)) AS minutes
  FROM [dbo].[BroadsoftCDRFact]
  WHERE bf_direction = 'Terminating' AND [bf_answerIndicator] = 'Y' AND [bf_callStartDateLocal] >= 20210301 AND [bf_callStartDateLocal] < 20210501
  GROUP BY [bf_callStartDateLocal] / 100, [bf_calledNumber]
  ORDER BY month, DNIS;