問題描述

我的系統中有反向調度.我使用下面顯示的函數來排除周末或將日期推回到星期五，如果在周末安排了向后調度，但如何排除假期?.例如，我想在假期或 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.

這篇關于向后調度以排除假期的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！