How to break my results down into Months

Question

SELECT 
        COUNT(CALL_KEY) AS CONTACTS,
        SUM(CASE WHEN answer_time IS NOT NULL AND A.ent_split IS NOT NULL AND call_type NOT LIKE 'PBX%' THEN 1 ELSE 0 END) AS CH,
        CAST((CAST(SUM(A.talk_time) 
     / (CASE WHEN COUNT(A.answer_time) = 0 THEN 1 ELSE COUNT(A.answer_time) END) AS float) + CAST(SUM(A.hold_time) AS float) 
     / (CASE WHEN COUNT(A.answer_time) = 0 THEN 1 ELSE COUNT(A.answer_time) END)) + CAST(SUM(A.work_time) AS float) 
     / CAST(COUNT(CASE WHEN A.answer_time > 0 THEN 1 ELSE 1 END) AS numeric(8, 2)) AS Int) AS AHT, 
        SUM(CASE WHEN abandon_time IS NOT NULL AND answer_time IS NULL THEN 1 ELSE 0 END) AS Total_ABN, 
        SUM(A.answer_speed) AS ASA,
        SUM(CASE WHEN answer_time IS NOT NULL AND A.ent_split IS NOT NULL AND call_type NOT LIKE 'PBX%' THEN 1 ELSE 0 END) + SUM(CASE WHEN abandon_time IS NOT NULL  AND answer_time IS NULL THEN 1 ELSE 0 END) AS CO


FROM    reporting_call_matrix AS A WITH (NOLOCK) 
WHERE  A.CALENDAR_DATE >= '5/31/2016' AND A.CALENDAR_DATE <= '7/19/2016' AND A.SPLIT IN (43) AND ( A.ent_split IS NOT NULL)

Here's My Results

CONTACTS    CH  AHT Total_ABN   ASA CO
17199          16744    640     448       580643   17192

Review this prior post ... [How to group by month from Date field using sql](http://stackoverflow.com/questions/14565788/how-to-group-by-month-from-date-field-using-sql) — JohnH, Jul 22 '16 at 16:22

score 0 · Answer 1 · answered Jul 22 '16 at 16:28

Assuming that the month can be obtained from the A.CALENDAR_DATE column

SELECT  Month(A.CALENDAR_DATE) as Month,
        COUNT(CALL_KEY) AS CONTACTS,
        SUM(CASE WHEN answer_time IS NOT NULL AND A.ent_split IS NOT NULL AND call_type NOT LIKE 'PBX%' THEN 1 ELSE 0 END) AS CH,
        CAST((CAST(SUM(A.talk_time) 
     / (CASE WHEN COUNT(A.answer_time) = 0 THEN 1 ELSE COUNT(A.answer_time) END) AS float) + CAST(SUM(A.hold_time) AS float) 
     / (CASE WHEN COUNT(A.answer_time) = 0 THEN 1 ELSE COUNT(A.answer_time) END)) + CAST(SUM(A.work_time) AS float) 
     / CAST(COUNT(CASE WHEN A.answer_time > 0 THEN 1 ELSE 1 END) AS numeric(8, 2)) AS Int) AS AHT, 
        SUM(CASE WHEN abandon_time IS NOT NULL AND answer_time IS NULL THEN 1 ELSE 0 END) AS Total_ABN, 
        SUM(A.answer_speed) AS ASA,
        SUM(CASE WHEN answer_time IS NOT NULL AND A.ent_split IS NOT NULL AND call_type NOT LIKE 'PBX%' THEN 1 ELSE 0 END) + SUM(CASE WHEN abandon_time IS NOT NULL  AND answer_time IS NULL THEN 1 ELSE 0 END) AS CO

FROM    reporting_call_matrix AS A WITH (NOLOCK) 
WHERE  A.CALENDAR_DATE >= '5/31/2016' AND A.CALENDAR_DATE <= '7/19/2016' AND A.SPLIT IN (43) AND ( A.ent_split IS NOT NULL)
Group by Month(A.CALENDAR_DATE)

How to break my results down into Months

1 Answers1