Home Backward Scheduling to Exclude Holidays

# Backward Scheduling to Exclude Holidays

user8066749
1#
user8066749 Published in 2017-11-14 00:14:17Z
 I have the backward scheduling in my system. i used function shown below to exclude weekend or push the date back to Friday if backward scheduling laid on the weekend but how can exclude holidays?. for example i want to push any date on 23th and 24th of Nov 2017 after the holiday or 28th . here is the code i used to skip the weekends Create function [dbo].[PreviousWorkDay]( @date date ) returns date as begin set @date = dateadd( day, -1, @date ) return ( select case datepart( weekday, @date ) when 7 then dateadd( day, -1, @date ) when 1 then dateadd( day, -2, @date ) else @date end ) end 
tarheel
2#
 In order to achieve this, you would have to do a couple things. 1) Create infrastructure to list out what dates are considered holidays. This is necessary for two reasons, A) some holidays move days every year (e.g. Thanksgiving), B) what holidays are not work days depends on the organization. 2) Just like HABO said, remove your dependence on the datepart/weekday as someone could change this setting on your instance and your existing logic would go haywire. Holiday Infrastructure create table dbo.holidays ( holiday_dt date not null ) insert into dbo.holidays values ('2017-11-23') --Thanksgiving (moves every year) , ('2017-12-25') --Christmas (same day every year)  Answer create function [dbo].[PreviousWorkDay]( @date date ) returns date as begin declare @date_rng int = 7 --dont think there would ever be 7 holiday/weekend days in a row , @ans date; with date_list as ( --use a Recursive CTE to generate a list of recent dates (assuming there is no table w/ each calendar day listed) select dateadd(d, -1*@date_rng, @date) as dt union all select dateadd(d,1,dt) as dt from date_list where 1=1 and dt < @date ) select @ans = max(sub.dt) from ( select dl.dt , case when datename(dw, dl.dt) in ('Saturday', 'Sunday') then 0 when h.holiday_dt is not null then 0 else 1 end as is_work_day from date_list as dl left join dbo.holidays as h on dl.dt = h.holiday_dt ) as sub where 1=1 and sub.is_work_day = 1 return @ans; end go  Example This function call  select dbo.PreviousWorkDay('2017-12-25')  would return 2017-12-22.