I have a yearly calendar stored in an sql table amd each day (date) has two markers: Holiday & Weekend.
When the specific date is a holiday, the Holiday entry is ‘1’
When the specific date is a weekend, the Weekend entry is ‘1’
When the specific date is a holiday & weekend, both Holiday and Weekend entries are ‘1’
Now, what I am trying to do is the followig:
- Count number of working days in each month
- if date is holiday, weekend or both, the count of that day should be equal to the date before
Below is an example:
| Date | Year | Month | Day | IsWeekend | IsHoliday | Counter |
|---|---|---|---|---|---|---|
| 2023.01.01 | 2023 | 1 | 1 | 1 | 1 | 0 |
| 2023.01.02 | 2023 | 1 | 2 | 0 | 1 | 0 |
| 2023.01.03 | 2023 | 1 | 3 | 0 | 0 | 1 |
| 2023.01.04 | 2023 | 1 | 4 | 0 | 0 | 2 |
| 2023.01.05 | 2023 | 1 | 5 | 0 | 0 | 3 |
| 2023.01.06 | 2023 | 1 | 6 | 0 | 0 | 4 |
| 2023.01.07 | 2023 | 1 | 7 | 1 | 0 | 4 |
| 2023.01.08 | 2023 | 1 | 8 | 1 | 0 | 4 |
| 2023.01.09 | 2023 | 1 | 9 | 0 | 0 | 5 |
| 2023.01.10 | 2023 | 1 | 10 | 0 | 0 | 5 |
So far, I have tried ROW_NUMBER() and setting a @Counter but with no luck
>Solution :
COUNT(CASE WHEN IsHoliday = 0 AND IsWeekend = 0 THEN 1 END) OVER(ORDER BY "Date")