DEV Community

loading...

Discussion on: What is a SQL query you are proud of?

Collapse
j1cordingley profile image
JCord

This will find the last business day it will skip weekends and holidays.

It start with a parameter @businessday I pass it to a CTE and check if that date is a Sunday or a monday if it is I take the bitwise NOT of the day of the week(sunday =1 which ~1 =-2,
Monday =2 which ~2= -3) else I just -1 then make the CTE an self referencing CTE to check of holidays with a table of dates of company holidays. I use a CTE because if a holiday landed on a Monday or Friday it need to then recheck if previous day was a weekend
again.

;with LB as (
    Select
        dateadd(day,  (case when datepart(weekday,@businesday) in(1,2) then  ~datepart(weekday,@businesday)  else -1 end )  ,@businesday) previousday
union all
    Select dateadd(day,  case when datepart(weekday,lb.previousday) in(1,2) then  ~datepart(weekday,lb.previousday)  else -1 end   ,lb.previousday )   from LB

    join dbo.Holidays h on h.HolidayDate=LB.previousday
)
Select min(previousday) from LB