sql – 使用营业时间和假期计算到期日期

我需要计算SLA的到期日期/结束日期.作为输入值,我有开始日期和时间跨度(以分钟为单位).此计算需要考虑营业时间,周末和假期.

我已经看到很多例子,其中输入是开始日期和结束日期,但是一直在努力寻找与上述输入值类似的东西.

这个问题有优雅的解决方案吗？有没有办法在不使用循环的情况下计算到期日期？如果不做类似以下可怕算法的事情,我想不出一种方法来进行计算：

>创建一个返回变量“截止日期”并将其设置为输入变量
“开始日期”
>创建一个控制变量“used minutes”并将其设置为0
>创建一个条件为“used minutes”< =“input timespan”的循环
>在循环内部,向“到期日期”返回变量添加第二个
>在循环内部,检查第二个是否在运行的几小时内
(查看营业时间,周末和假期).如果是这样,增量
控制变量“used minutes”by 1.
>退出循环后,返回变量“截止日期”

解决方法

这是日历表：

CREATE TABLE [dbo].[tblDay](
    [dt] [datetime] NOT NULL,[dayOfWk] [int] NULL,[dayOfWkInMo] [int] NULL,[isWeekend] [bit] NOT NULL,[holidayID] [int] NULL,[workingDayCount] [int] NULL,CONSTRAINT [PK_tblDay] PRIMARY KEY CLUSTERED 
(
    [dt] ASC
)WITH (PAD_INDEX  = OFF,STATISTICS_NORECOMPUTE  = OFF,IGNORE_DUP_KEY = OFF,ALLOW_ROW_LOCKS  = ON,ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

这是我几天填充表格的方式：

CREATE PROCEDURE [dbo].[usp_tblDay]
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE  
        @Dt datetime,@wkInMo int,@firstDwOfMo int,@holID int,@workDayCount int,@weekday int,@month int,@day int,@isWkEnd bit

    set @workDayCount = 0
    SET @Dt = CONVERT( datetime,'2008-01-01' ) 
    while @dt < '2020-01-01'
    begin
        delete from tblDay where dt = @dt

        set @weekday = datepart( weekday,@Dt )
        set @month = datepart(month,@dt)
        set @day = datepart(day,@dt)

        if @day = 1  -- 1st of mo
            begin
                set @wkInMo = 1
                set @firstDwOfMo = @weekday
            end

        if ((@weekday = 7) or (@weekday = 1)) 
            set @isWkEnd = 1 
        else 
            set @isWkEnd = 0

        if @isWkEnd = 0 and (@month = 1 and @day = 1) 
            set @holID=1        -- new years on workday
        else if @weekday= 6 and (@month = 12 and @day = 31) 
            set @holID=1        -- holiday on sat,change to fri
        else if @weekday= 2 and (@month = 1 and @day = 2) 
            set @holID=1        -- holiday on sun,change to mon

        else if @wkInMo = 3 and @weekday= 2 and @month = 1 
            set @holID = 2      -- mlk

        else if @wkInMo = 3 and @weekday= 2 and @month = 2 
            set @holID = 3      -- President’s

        else if @wkInMo = 4 and @weekday= 2 and @month = 5 and datepart(month,@dt+7) = 6
            set @holID = 4      -- memorial on 4th mon,no 5th
        else if @wkInMo = 5 and @weekday= 2 and @month = 5 
            set @holID = 4      -- memorial on 5th mon

        else if @isWkEnd = 0 and (@month = 7 and @day = 4) 
            set @holID=5        -- July 4 on workday
        else if @weekday= 6 and (@month = 7 and @day = 3) 
            set @holID=5        -- holiday on sat,change to fri
        else if @weekday= 2 and (@month = 7 and @day = 5) 
            set @holID=5        -- holiday on sun,change to mon

        else if @wkInMo = 1 and @weekday= 2 and @month = 9 
            set @holID = 6      -- Labor

        else if @isWkEnd = 0 and (@month = 11 and @day = 11) 
            set @holID=7        -- Vets day on workday
        else if @weekday= 6 and (@month = 11 and @day = 10) 
            set @holID=7        -- holiday on sat,change to fri
        else if @weekday= 2 and (@month = 11 and @day = 12) 
            set @holID=7        -- holiday on sun,change to mon

        else if @wkInMo = 4 and @weekday= 5 and @month = 11 
            set @holID = 8      -- thx

        else if @holID = 8
            set @holID = 9      -- dy after thx

        else if @isWkEnd = 0 and (@month = 12 and @day = 25) 
            set @holID=10       -- xmas day on workday
        else if @weekday= 6 and (@month = 12 and @day = 24) 
            set @holID=10       -- holiday on sat,change to fri
        else if @weekday= 2 and (@month = 12 and @day = 26) 
            set @holID=10       -- holiday on sun,change to mon
        else
            set @holID = null

        insert into tblDay select @dt,@weekday,@wkInMo,@isWkEnd,@holID,@workDayCount

        if @isWkEnd=0 and @holID is null 
            set @workDayCount = @workDayCount + 1

        set @dt = @dt + 1
        if datepart( weekday,@Dt ) = @firstDwOfMo 
            set @wkInMo = @wkInMo + 1
    end
END

我也有假期表,但每个人的假期都不同：

holidayID   holiday rule description
1   New Year's Day  Jan. 1
2   Martin Luther King Day  third Mon. in Jan.
3   Presidents' Day third Mon. in Feb.
4   Memorial Day    last Mon. in May
5   Independence Day    4-Jul
6   Labor Day   first Mon. in Sept
7   Veterans' Day   Nov. 11
8   Thanksgiving    fourth Thurs. in Nov.
9   Fri after Thanksgiving  Friday after Thanksgiving
10  Christmas Day   Dec. 25

HTH

（编辑：哈尔滨站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!