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  EXECUTE IMMEDIATE $$
-- Snowflake Scripting code
DECLARE 
    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';

    LET res RESULTSET := (SELECT
        :monthLabel Month, CONCAT( CONCAT( lcd_city, ' - '), lcd_stateProvince ) Location, REPLACE(lower(bud_broadsoftUserId), '.', ' - ') AS UserId, REPLACE(lower(bud_broadsoftUserDisplayName), ',', ' - ') AS 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) InboundCalls,
        SUM(CASE WHEN bf_direction = 'Terminating' AND bf_answerIndicator IS NOT NULL AND bf_answerIndicator = 'Y' THEN 1 ELSE 0 END) InboundAnsweredCalls,
        to_number(SUM(CASE WHEN bf_direction = 'Terminating' AND bf_answerIndicator IS NOT NULL THEN bf_billDuration ELSE 0 END) / 60.0) InboundMinutes,
        SUM(CASE WHEN bf_direction = 'Originating' AND bf_answerIndicator IS NOT NULL THEN 1 ELSE 0 END) OutboundCalls,
        SUM(CASE WHEN bf_direction = 'Originating' AND bf_answerIndicator IS NOT NULL AND bf_answerIndicator = 'Y' THEN 1 ELSE 0 END) OutboundAnsweredCalls,
        to_number(SUM(CASE WHEN bf_direction = 'Originating' AND bf_answerIndicator IS NOT NULL THEN bf_billDuration ELSE 0 END) / 60.0) OutboundMinutes,
        SUM(CASE WHEN bf_answerIndicator IS NOT NULL THEN 1 ELSE 0 END) TotalCalls,
        SUM(CASE WHEN bf_answerIndicator IS NOT NULL AND bf_answerIndicator = 'Y' THEN 1 ELSE 0 END) TotalAnsweredCalls,
        to_number(SUM(CASE WHEN bf_answerIndicator IS NOT NULL THEN bf_billDuration ELSE 0 END) / 60.0) TotalMinutes,
        CASE WHEN lower(bud_broadsoftUserId) LIKEILIKE 'aa-%' THEN 'AUTO ATTENDANT'
            ELSE CASE WHEN lower(bud_broadsoftUserId) LIKEILIKE 'cc-%' THEN 'CALL CENTER'
                ELSE CASE WHEN lower(bud_broadsoftUserId) LIKEILIKE 'hg-%' THEN 'HUNT GROUP' ELSE
                    CASE WHEN lower(bud_broadsoftUserId) LIKEILIKE 'cb-%' THEN 'CONF BRIDGE' ELSE
                        CASE WHEN lower(bud_broadsoftUserId) LIKEILIKE 'casa-%' THEN 'AMP' ELSE 'USER' END END END END END UserType
    FROM
        BROADSOFTUSERDIMENSION
            LEFT OUTER JOIN BroadsoftCDRFact ON bud_id = bf_userFk AND bf_callStartDateLocal >= :fDate AND bf_callStartDateLocal <= :tDate
            LEFT OUTER JOIN LocationDimension 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 lower(bud_broadsoftUserId), bud_broadsoftExtension, lower(bud_broadsoftUserDisplayName), CONCAT( CONCAT( lcd_city, ' - '), lcd_stateProvince )
    ORDER BY Location, UserId, Extension, UserName);
    return TABLE(res);
END;
$$
;

-- Sum call counts and call durations for inbound answered calls for daileddialed numbers by month 
-- Broadsoft CDR Fact


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