Link to home
Create AccountLog in
Avatar of tmajor99

asked on

MS SQL Case/When Condition Record Counter

How can i count records based on a case/when condition.  I need to count the number of records that were update by each month. I am using a case conditions to identify the month the record update took place but i do not know how to adjust a record counter when the condition is true.  

ID       UPDT_Date
----   ------------
1      01/13/2022 
2      01/21/2022
3      01/04/2022
4      02/01/2022
5      03/14/2022 

    WHEN UPDT_DATE like '01/%' THEN ??? How do i count?
    WHEN UPDT_DATE like '02/%' THEN ??? How do i count?
    END AS Counter
FROM MyTable 

The end result should be a report that looks like this:

Month    Number Of Record Updates
JAN       3
FEB       1
MARCH     1 

Open in new window

Avatar of Kyle Abrahams, PMP
Kyle Abrahams, PMP
Flag of United States of America image

Link to home
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Assuming UPDT_Date is a date and not a string, try this:
	format(UPDT_Date,'MMM') month,
from mytable
group by 
order by

Open in new window

Eyeballeth thee excellent article I wrote SQL Server CASE Solutions, then scroll down to CASE can also be used with aggregates such as SUM or COUNT, which offers up the same solution Kyle posted above.

   COUNT(CASE WHEN group_id = 1 THEN name END) as group_1_count, 
   COUNT(CASE WHEN group_id = 2 THEN name END) as group_2_count, 
   COUNT(CASE WHEN group_id = 3 THEN name END) as group_3_count,
   COUNT(CASE WHEN group_id = 4 THEN name END) as group_4_count,
   COUNT(CASE WHEN group_id = 5 THEN name END) as group_5_count,
   COUNT(CASE WHEN group_id = 6 THEN name END) as group_6_count,
   COUNT(CASE WHEN group_id = 7 THEN name END) as group_7_count
FROM some_table

Open in new window

Can I ask why you accepted that answer?
It returns the data as columns which you will then need to unpivot.
You said you desired rows.