Skip to content
Advertisement

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:

Calendar looks like this:

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

If Friday is a holiday, it will display this:

If Friday and Thursday are holidays, it should display:

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?

Advertisement

Answer

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

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.

User contributions licensed under: CC BY-SA
9 People found this is helpful
Advertisement