how to use partition and keep consistency

Question

I have a dataset of all the dates from 1982 until today, in the CALENDAR table. Each record (date) in the table had an indication of whether it's a business day or not. I am trying to number the rows so that it starts from 1 and continuously run through the dates, but when it gets to a date that is NOT a working day, it will receive the same number as the previous date, and continue the numbering - NOT partition and restart the numbering. This is the code I used:

SELECT t1.thedate,
        t1.working_day,
        Rank() Over(PARTITION BY Working_Day ORDER BY TheDate) AS test
FROM CAL t1
WHERE 1=1
ORDER BY 1

Answer 1

Don't use RANK, simply sum over 1/0:

sum(case when Working_Day = 'Y' then 1 else 0 end)
over (ORDER BY TheDate
      rows unbounded preceding)

Or a bit shorter, count non-null rows:

count(case when Working_Day = 'Y' then 1 end)
over (ORDER BY TheDate
      rows unbounded preceding)