向后调度以排除假期

2021-09-10 00:00:00 tsql sql-server

我的系统中有反向调度.我使用下面显示的函数来排除周末或将日期推回到星期五，如果在周末安排了向后调度，但如何排除假期?.例如，我想在假期或 28 日之后推送 2017 年 11 月 23 日和 24 日的任何日期.

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

推荐答案

为了实现这一点，您需要做一些事情.

In order to achieve this, you would have to do a couple things.

1) 创建基础设施以列出哪些日期被视为假期.这是必要的，原因有两个:A) 某些假期每年都会移动几天(例如感恩节)，B) 哪些假期不是工作日取决于组织.

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) 就像 HABO 所说的那样，消除您对 datepart/weekday 的依赖，因为有人可以在您的实例上更改此设置，而您现有的逻辑会失控.

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.

假日基础设施

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)

回答

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

示例

这个函数调用

  select dbo.PreviousWorkDay('2017-12-25')

将返回 2017-12-22.

相关文章