Link to home
Create AccountLog in
Avatar of tmajor99
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.  

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

Select
CASE
    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

ASKER CERTIFIED SOLUTION
Avatar of Kyle Abrahams, PMP
Kyle Abrahams, PMP
Flag of United States of America image

Link to home
membership
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:
select
	format(UPDT_Date,'MMM') month,
	count(*)
from mytable
group by 
	datepart(month,UPDT_Date),
	format(UPDT_Date,'MMM')
order by
	datepart(month,UPDT_Date)

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.

SELECT 
   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.