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?
Advertisement
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.