How to get the last work day in SQL Server date dimension?

We are using a dimension table for dates based on this blog post. There is a view which gives for each date if it is a week day/ weekend or if that date is a holiday.

TheDate	IsWeekend	IsHoliday
2022-04-13	0	0
2022-04-14	0	0
2022-04-15	0	1
2022-04-16	1	0
2022-04-17	1	1
2022-04-18	0	1
2022-04-19	0	0
2022-04-20	0	0

We are using a window function with with LAG() to get the last workday on weekends:

SELECT [TheDate]
    ,[TheDayName]
    ,CASE 
        WHEN [TheDayOfWeek] = 1
            THEN LAG([TheDate], 2) OVER (
                    ORDER BY [TheDate]
                    )
        WHEN [TheDayOfWeek] = 2
            THEN LAG([TheDate], 3) OVER (
                    ORDER BY [TheDate]
                    )
        ELSE LAG([TheDate], 1) OVER (
                ORDER BY [TheDate]
                )
        END AS LastWorkDay
FROM [dbo].[TheCalendar]

The result is as expected:

TheDate	TheDayName	LastWorkDay
2022-04-13	Wednesday	2022-04-12
2022-04-14	Thursday	2022-04-13
2022-04-15	Friday	2022-04-14
2022-04-16	Saturday	2022-04-15
2022-04-17	Sunday	2022-04-15
2022-04-18	Monday	2022-04-15
2022-04-19	Tuesday	2022-04-18

I am looking now how the window function can include holidays, so the Workday before Tuesday, April 19 is actually Thursday, April 15 because Monday (4/16) and Friday (4/15) are holidays and Sunday (4/17) and Saturday (4/16) are Weekend (Well Sunday in fact is both). I tried to use a cursor to iterate dates down to the next work day but it is everything than performant. I am thinking now to materialize the last workday for weekends and then again doing the same exercise for holidays. What could be a more intelligent approach?

Solution

As @lptr commented, you can use max...over query to acheive the requirement. Made few changes in that query to work for all use cases. Below is the query.

Query:

select *, case when IsWeekend=0 and IsHoliday=0 then lag(TheDate,1) over (order by TheDate) else
  max(case when IsWeekend=0 and IsHoliday=0 then theDate end) 
  over(order by TheDate rows between 10 preceding and 1 preceding) end
  from [dbo].[TheCalendar]

This query selects all columns from the table TheCalendar and uses a CASE statement to check if the current date is a weekend or holiday. If it is not, then it uses the LAG function to get the previous non-holiday and non-weekend date. If it is a weekend or holiday, then it uses a window function to get the maximum date for the previous 10 non-holiday and non-weekend days. The query you provided is correct.

Input:

TheDate	IsWeekend	IsHoliday
2022-04-13	False	False
2022-04-14	False	False
2022-04-15	False	True
2022-04-16	True	False
2022-04-17	True	True
2022-04-18	False	True
2022-04-19	False	False
2022-04-20	False	False

Output:

TheDate	IsWeekend	IsHoliday	PrevDay
2022-04-13	False	False	null
2022-04-14	False	False	2022-04-13
2022-04-15	False	True	2022-04-14
2022-04-16	True	False	2022-04-14
2022-04-17	True	True	2022-04-14
2022-04-18	False	True	2022-04-14
2022-04-19	False	False	2022-04-18
2022-04-20	False	False	2022-04-19

db<>fiddle