SQL Server, running total, reset for each month and sum again

Question

I have a calendar table where working days are marked. Now I need a running total called "current_working_day" which sums up the working days until the end of a month and restarts again.

This is my query:

select
WDAYS.Date,
WDAYS.DayName,
WDAYS.WorkingDay,
sum(WDAYS.WorkingDay) OVER(order by (Date), MONTH(Date), YEAR(Date))  as 'current_working_day',
sum(WDAYS.WorkingDay) OVER(PARTITION by YEAR(WDAYS.Date), MONTH(WDAYS.Date) ) total_working_days_per_month
from WDAYS
where YEAR(WDAYS.Date) = 2022

This is my current output

Date	DayName	WorkingDay	current_working_day	total_working_days_per_month
2022-01-27	Thursday	1	19	21
2022-01-28	Friday	1	20	21
2022-01-29	Saturday	0	20	21
2022-01-30	Sunday	0	20	21
2022-01-31	Monday	1	21	21
2022-02-01	Tuesday	1	22	20
2022-02-02	Wednesday	1	23	20
2022-02-03	Thursday	1	24	20

But the column "current_workind_day" should be like this

Date	DayName	WorkingDay	current_working_day	total_working_days_per_month
2022-01-27	Thursday	1	19	21
2022-01-28	Friday	1	20	21
2022-01-29	Saturday	0	20	21
2022-01-30	Sunday	0	20	21
2022-01-31	Monday	1	21	21
2022-02-01	Tuesday	1	1	20
2022-02-02	Wednesday	1	2	20
2022-02-03	Thursday	1	3	20

Thanks for any advice.

Answer 1

You can try to use PARTITION by with EOMONTH function which might get the same result but better performance, then you might only need to order by Date instead of using the function with the date.

select
  WDAYS.Date,
  WDAYS.DayName,
  WDAYS.WorkingDay,
  sum(WDAYS.WorkingDay) OVER(PARTITION by EOMONTH(WDAYS.Date) order by Date)  as 'current_working_day',
  sum(WDAYS.WorkingDay) OVER(PARTITION by EOMONTH(WDAYS.Date) ) total_working_days_per_month
from WDAYS
where YEAR(WDAYS.Date) = 2022