Page History

/*
 * This usage reports is based on the BroadsoftUserDimension table so all users are represented even if they did not gernerate any traffic.
 * The columns in this report are:
 * Month,Location,User Id,User Name,Extension,Inbound Calls,Inbound Answered Calls,Inbound Minutes,Outbound Calls,Outbound Answered Calls,Outbound Minutes,Total Calls,Total Answered Calls,Total Minutes,User Type
 */

DECLARE @monthLabel CHAR(7),
		@fDate INT,
		@tDate INT;

SELECT @monthLabel = [rdd_label], @fDate = [rdd_firstDate], @tDate = [rdd_lastDate] FROM [dbo].[ReportDatesDimension] WHERE [rdd_label] = '2022-05';

SELECT
	@monthLabel [Month], CONCAT( CONCAT( [lcd_city], ' - '), [lcd_stateProvince] ) [Location], REPLACE([bud_broadsoftUserId], '.', ' - ') AS [User Id], REPLACE([bud_broadsoftUserDisplayName], ',', ' - ') AS [User Name],
	CASE WHEN [bud_broadsoftExtension] IS NOT NULL THEN [bud_broadsoftExtension] ELSE '' END [Extension],
	SUM(CASE WHEN [bf_direction] = 'Terminating' AND [bf_answerIndicator] IS NOT NULL THEN 1 ELSE 0 END) [Inbound Calls],
	SUM(CASE WHEN [bf_direction] = 'Terminating' AND [bf_answerIndicator] IS NOT NULL AND [bf_answerIndicator] = 'Y' THEN 1 ELSE 0 END) [Inbound Answered Calls],
	CONVERT(DECIMAL(10,2), SUM(CASE WHEN [bf_direction] = 'Terminating' AND [bf_answerIndicator] IS NOT NULL THEN [bf_billDuration] ELSE 0 END) / 60.0) [Inbound Minutes],
	SUM(CASE WHEN [bf_direction] = 'Originating' AND [bf_answerIndicator] IS NOT NULL THEN 1 ELSE 0 END) [Outbound Calls],
	SUM(CASE WHEN [bf_direction] = 'Originating' AND [bf_answerIndicator] IS NOT NULL AND [bf_answerIndicator] = 'Y' THEN 1 ELSE 0 END) [Outbound Answered Calls],
	CONVERT(DECIMAL(10,2), SUM(CASE WHEN [bf_direction] = 'Originating' AND [bf_answerIndicator] IS NOT NULL THEN [bf_billDuration] ELSE 0 END) / 60.0) [Outbound Minutes],
	SUM(CASE WHEN [bf_answerIndicator] IS NOT NULL THEN 1 ELSE 0 END) [Total Calls],
	SUM(CASE WHEN [bf_answerIndicator] IS NOT NULL AND [bf_answerIndicator] = 'Y' THEN 1 ELSE 0 END) [Total Answered Calls],
	CONVERT(DECIMAL(10,2), SUM(CASE WHEN [bf_answerIndicator] IS NOT NULL THEN [bf_billDuration] ELSE 0 END) / 60.0) [Total Minutes],
	CASE WHEN [bud_broadsoftUserId] LIKE 'aa-%' THEN 'AUTO ATTENDANT'
		ELSE CASE WHEN [bud_broadsoftUserId] LIKE 'cc-%' THEN 'CALL CENTER'
			ELSE CASE WHEN [bud_broadsoftUserId] LIKE 'hg-%' THEN 'HUNT GROUP' ELSE
				CASE WHEN [bud_broadsoftUserId] LIKE 'cb-%' THEN 'CONF BRIDGE' ELSE
					CASE WHEN [bud_broadsoftUserId] LIKE 'casa-%' THEN 'AMP' ELSE 'USER' END END END END END [User Type]
FROM
	[dbo].[BroadsoftUserDimension] WITH (NOLOCK)
		LEFT OUTER JOIN [dbo].[BroadsoftCDRFact] WITH(NOLOCK) ON [bud_id] = [bf_userFk] AND [bf_callStartDateLocal] >= @fDate AND [bf_callStartDateLocal] <= @tDate
		LEFT OUTER JOIN [dbo].[LocationDimension] WITH(NOLOCK) ON [bud_locationId] = [lcd_location]
WHERE
	[bud_id] <> 0 AND [bud_broadsoftUserId] NOT LIKE '%-Default' AND [bud_broadsoftUserId] NOT LIKE '%_MOH' AND [bud_broadsoftUserId] NOT LIKE '%_VMR' AND
	[bud_broadsoftUserId] NOT LIKE 'vb-%'
GROUP BY [bud_broadsoftUserId], [bud_broadsoftExtension], [bud_broadsoftUserDisplayName], CONCAT( CONCAT( [lcd_city], ' - '), [lcd_stateProvince] )
ORDER BY [Location], [User Id], [Extension], [User Name];

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],

	-- Percentage of calls abandoned
	CAST (CASE WHEN SUM( CASE [bf_direction] WHEN 'Terminating' THEN 1 ELSE 0 END ) > 0
		THEN
			CAST((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 DECIMAL) /
					SUM( CASE [bf_direction] WHEN 'Terminating' THEN 1 ELSE 0 END )
		ELSE 0
		END * 100.0 AS DECIMAL(4,1)) AS [Percent Abandoned],

	-- 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],

	-- Precent handled calls (calls answered by a person)
	CAST (CASE WHEN SUM( CASE WHEN [bf_direction] = 'Originating' AND [bf_answerIndicator] = 'Y' THEN 1 ELSE 0 END ) > 0
		THEN
			CAST (SUM( CASE WHEN [bf_direction] = 'Originating' AND [bf_answerIndicator] = 'Y' AND bf_calledNumber <> '5000' THEN 1 ELSE 0 END ) AS DECIMAL) /
					SUM( CASE WHEN [bf_direction] = 'Originating' AND [bf_answerIndicator] = 'Y' THEN 1 ELSE 0 END )
		ELSE 0
		END * 100.0 AS DECIMAL(5,2)) AS [Percent 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],

	-- Average duration of handled calls in minutes
	CAST (CASE WHEN SUM( CASE WHEN [bf_direction] = 'Originating' AND [bf_answerIndicator] = 'Y' AND bf_calledNumber <> '5000' THEN 1 ELSE 0 END ) > 0
		THEN
			CAST (SUM( CASE WHEN [bf_direction] = 'Originating' AND [bf_answerIndicator] = 'Y' AND bf_calledNumber <> '5000' THEN [bf_billDuration] ELSE 0 END ) AS DECIMAL) /
				SUM( CASE WHEN [bf_direction] = 'Originating' AND [bf_answerIndicator] = 'Y' AND bf_calledNumber <> '5000' THEN 1 ELSE 0 END )
		ELSE 0
	END / 60.0 AS DECIMAL(6,2)) AS [Average 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],

	-- Average speed of answer (ASA) (wait time) in minutes
	CAST (CASE WHEN SUM( CASE WHEN [bf_direction] = 'Originating' AND [bf_answerIndicator] = 'Y' AND bf_calledNumber <> '5000' THEN 1 ELSE 0 END ) > 0
		THEN
			CAST (SUM( CASE WHEN [bf_direction] = 'Originating' AND [bf_answerIndicator] = 'Y' AND bf_calledNumber <> '5000' THEN [bf_ringDuration] ELSE 0 END ) AS DECIMAL) /
				SUM( CASE WHEN [bf_direction] = 'Originating' AND [bf_answerIndicator] = 'Y' AND bf_calledNumber <> '5000' THEN 1 ELSE 0 END )
		ELSE 0
		END / 60.0 AS DECIMAL(5,2)) AS [ASA]
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];

-- Sum call counts and call durations for inbound answered calls for dailed numbers by month

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;