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],
 
    -- 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],
 
    -- 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],
 
    -- 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],
 
    -- Ring duration in minutes
    CAST (SUM( CASE WHEN [bf_direction] = 'Originating' AND [bf_answerIndicator] = 'Y' AND bf_calledNumber <> '5000' THEN [bf_ringDuration] ELSE 0 END ) / 60.0 AS DECIMAL(8,2)) AS [Ring Duration],
 
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];