sql server - Backward Scheduling to Exclude Holidays

Question

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 

Answer 1

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.