Search code examples
sqlsql-serverif-statementcounter

SQL Counter with condition

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:

  1. Count number of working days in each month
  2. 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")