CASE Statement Help

1

Need some help with adding a CASE statement for the RunningAccepted column similar to what's happening in the accepted_ct column above that in order to make some adjustments to some counts. I'm not quite sure how I should express it logically.

SELECT DATEADD(DAY, 1, EOMONTH(sts.[Status Change Date], - 1)) sc_date,
CASE
WHEN DATEADD(DAY, 1, EOMONTH(sts.[Status Change Date], - 1)) = '2022-05-01'
THEN count(DISTINCT a.id) + 73
WHEN DATEADD(DAY, 1, EOMONTH(sts.[Status Change Date], - 1)) = '2022-06-01'
THEN count(DISTINCT a.id) -9
WHEN DATEADD(DAY, 1, EOMONTH(sts.[Status Change Date], - 1)) = '2022-07-01'
THEN count(DISTINCT a.id) - 351
ELSE count(DISTINCT a.id)
END AS accepted_ct,
sum(count(DISTINCT a.id)) OVER (
ORDER BY DATEADD(DAY, 1, EOMONTH(sts.[Status Change Date], - 1)))
RunningAccepted,

2 
SELECT DATEADD(DAY, 1, EOMONTH(sts.[Status Change Date], - 1)) sc_date,
count(DISTINCT a.id) + CASE
WHEN DATEADD(DAY, 1, EOMONTH(sts.[Status Change Date], - 1)) = '2022-05-01'
THEN 73
WHEN DATEADD(DAY, 1, EOMONTH(sts.[Status Change Date], - 1)) = '2022-06-01'
THEN -9
WHEN DATEADD(DAY, 1, EOMONTH(sts.[Status Change Date], - 1)) = '2022-07-01'
THEN -351
ELSE 0
END AS accepted_ct,
1 Like
3

This works, but I'm still getting an inflated RunningAccepted sum. For some reason RunningAccepted for the last month isn't being added to this months accepted_ct count which is the new RunningAccepted total for this month.

4

If you want some type of running total, that will require a different type of calc, such as COUNT() OVER or SUM() OVER.