How to show counts month after month

Transact-SQL
1

This code:

SELECT '1) Total non-employees terminated' AS DESCRIPTION,
  COUNT(*) AS AUGUST
FROM v_xyz_table
WHERE EMPLOYEE_STATUS_DESCR = 'INACTIVE'
AND update_by     = 'E_L_T~dbo'
AND update_on    >= DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) - 2, 0)
AND update_on     < DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0)

Produces the following results:

DESCRIPTION                           AUGUST
----------------------------------    ---------------
1) Total non-employees terminated     496

I would like to put a column called SEPTEMBER with September's count just to the right of the AUGUST column. To get September's count, I would use another query somehow and change the 2 to a 1 in the query. But how do I add the column for it? Do I need to use a JOIN or a PIVOT or something? Then in the future I'd like to have Oct, Nov, Dec, etc.

Thanks a lot!

2 
SELECT '1) Total non-employees terminated' AS DESCRIPTION
, SUM(CASE WHEN update_on >= '01 Aug 2017' AND update_on < '01 Sep 2017' THEN 1 ELSE 0 END) AS [August]
, SUM(CASE WHEN update_on >= '01 Sep 2017' AND update_on < '01 Oct 2017' THEN 1 ELSE 0 END) AS [September]
FROM v_xyz_table
WHERE EMPLOYEE_STATUS_DESCR = 'INACTIVE'
AND update_by     = 'E_L_T~dbo'
AND update_on    >= '01 Aug 2017'
AND update_on     < '01 Oct 2017'

You can change my Human Dates to something appropriately "calculated" :slight_smile: , much as you had them in your original

1 Like
3

Thank you so much for your quick response! This is perfect. One last question: what exactly does that 'THEN 1 ELSE 0" part do? I think I have a general idea. If it meets the condition then display the column, and if not, then don't?

4

Its a running total SUM(). If it meets the condition then ADD ONE, if not then ADD ZERO. Bit of a (seemingly) cumbersome way to get a conditional total, but that's how it is most commonly done.

1 Like