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
For further actions, you may consider blocking this person and/or reporting abuse
We're a place where coders share, stay up-to-date and grow their careers.
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