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
 */  -- Snowflake Scripting code

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

SELECT @monthLabel = [rdd_label], @fDate = [rdd_firstDate], @tDate = [rdd_lastDate] FROM [dbo].[ReportDatesDimension] WHERE [rdd_label]
    monthLabel VARCHAR;
    fDate INTEGER;
    tDate INTEGER;
BEGIN
    SELECT rdd_label, rdd_firstDate, rdd_lastDate INTO :monthLabel, :fDate, :tDate  FROM EVOLVEIP.PUBLIC.REPORTDATESDIMENSION WHERE rdd_label = '2022-05';

SELECT
	@monthLabel [Month]    LET res RESULTSET := (SELECT
        :monthLabel Month, CONCAT( CONCAT( [lcd_city], ' - '), [lcd_stateProvince] ) [Location], REPLACE([bud_broadsoftUserId], '.', ' - ') AS [User Id]UserId, REPLACE([bud_broadsoftUserDisplayName], ',', ' - ') AS [User Name]UserName,
	        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]InboundCalls,
	        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), END) InboundAnsweredCalls,
        to_number(SUM(CASE WHEN [bf_direction] = 'Terminating' AND [bf_answerIndicator] IS NOT NULL THEN [bf_billDuration] ELSE 0 END) / 60.0) [Inbound Minutes]InboundMinutes,
	        SUM(CASE WHEN [bf_direction] = 'Originating' AND [bf_answerIndicator] IS NOT NULL THEN 1 ELSE 0 END) [Outbound Calls]OutboundCalls,
	        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), OutboundAnsweredCalls,
        to_number(SUM(CASE WHEN [bf_direction] = 'Originating' AND [bf_answerIndicator] IS NOT NULL THEN [bf_billDuration] ELSE 0 END) / 60.0) [Outbound Minutes],
	) / 60.0) OutboundMinutes,
        SUM(CASE WHEN [bf_answerIndicator] IS NOT NULL THEN 1 ELSE 0 END) [Total Calls]TotalCalls,
	        SUM(CASE WHEN [bf_answerIndicator] IS NOT NULL AND [bf_answerIndicator] = 'Y' THEN 1 ELSE 0 END) [Total Answered Calls],
	CONVERT(DECIMAL(10,2), TotalAnsweredCalls,
        to_number(SUM(CASE WHEN [bf_answerIndicator] IS NOT NULL THEN [bf_billDuration] ELSE 0 END) / 60.0) [Total Minutes]TotalMinutes,
	        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)
		 UserType
    FROM
        BROADSOFTUSERDIMENSION
            LEFT OUTER JOIN [dbo].[BroadsoftCDRFact] WITH(NOLOCK) BroadsoftCDRFact ON [bud_id] = [bf_userFk] AND [bf_callStartDateLocal] >= @fDate:fDate AND [bf_callStartDateLocal] <= @tDate
		:tDate
            LEFT OUTER JOIN [dbo].[LocationDimension] WITH(NOLOCK) ON [bud_locationId] = [lcd_location]
WHERE
	[    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]UserId, Extension, UserName);
    return TABLE(res);
END;
$$
;

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

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

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

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

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

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

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

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

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

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

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

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

	-- 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] >= 2021041420220414 AND [bf_callStartDateLocal] < 2021041520220415 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 -- Broadsoft CDR Fact

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;   ORDER BY month, DNIS;