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.
CodePudding user response：
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
CodePudding user response：
You could just calculate the sum over a partitioned bucket based on the Month and Year of the date column.
select
WDAYS.Date,
WDAYS.DayName,
WDAYS.WorkingDay,
sum(WDAYS.WorkingDay) OVER(partition by YEAR(date),MONTH(Date) order by
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