Scale CASE WHEN to retrieve non-holiday dates

I have table containing records with start and end dates, so I have to grab every record that started on a Friday, and if any Friday is a holiday, get Thursday, and if Friday and Thursday are holidays, get Wednesday and so on…

Currently I have two tables TableA for the records and Calendar to get holidays.

My TableA look like this:

id beg         end
--------------------------
1  11/01/2019  14/01/2019
2  10/01/2019  14/01/2019
3  09/01/2019  15/01/2019
...

Calendar looks like this:

iddate    date        weekday isholiday
---------------------------------------
20190109  09/01/2019  3       0 --0=False / 1=True
20190110  10/01/2019  4       0
20190111  11/01/2019  5       1
...

For both tables I use WHERE EXISTS to get Fridays and the holiday conditional which actually displays results:

SELECT *
FROM TableA t
WHERE EXISTS (SELECT 1
              FROM dbo.Calendar AS c
              WHERE c.date = CAST(t.BEG AS DATETIME)
                AND c.weekday = CASE
                                    WHEN (C.weekday = 5 AND C.isholiday = 0)  
                                       THEN 5 --keep friday
                                    --WHEN ((C.weekday= 4 AND C.isholiday= 1) OR (C.weekday= 5 AND C.isholiday= 1)) THEN 3 --keep wednesday / not working
                                       ELSE 4 --keep thursday
                                    END
                AND DATEPART(yyyy, T.beg) = 2019)
ORDER BY 
    CAST(INI AS [datetime])

If Friday is a holiday, it will display this:

id beg         end
--------------------------
2  10/01/2019  14/01/2019
3  09/01/2019  15/01/2019

If Friday and Thursday are holidays, it should display:

id beg         end
--------------------------
3  09/01/2019  15/01/2019

Scaling it up with this WHEN ((C.weekday= 4 AND C.isholiday= 1) OR (C.weekday= 5 AND C.isholiday= 1)) just breaks the entire query and I get -almost- random dates. Any way to scaling it up without extending the query too much?

Answer

Based on your comment, I would use a common table expression:

;With cte_Last_Day_Of_Week
As
(
    Select 
        c.iddate
        , c.date
        , Row_Number() Over (Partition By DatePart(Week, c.date) Order By c.date Desc) As RN
    From dbo.Calendar As c
    Where c.isholiday = 0
)

The Row_Number() will flag the date of the last non-holiday day in each week with a value of 1. You can then join against that as you would a table.

Let me know if that makes sense.

Advertisement

Answer