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