User Call Statistics
Download SQL file: CDR-UsageReportAllUsers.sql
The example below queries the data warehouse for data surrounding user calls including how many were taken (answered) and made (placed) and what the call duration for these categories
The data selected based on a specific month and groups the data by location and user
Code Block | ||||||
---|---|---|---|---|---|---|
| ||||||
/*
* 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]; |
Hunt Group Statistics
Download SQL file: CDR-HuntGroupStats-Complete.sql
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
...