我需要计算SLA的到期日期/结束日期.作为输入值,我有开始日期和时间跨度(以分钟为单位).此计算需要考虑营业时间,周末和假期.
我已经看到很多例子,其中输入是开始日期和结束日期,但是一直在努力寻找与上述输入值类似的东西.
这个问题有优雅的解决方案吗？有没有办法在不使用循环的情况下计算到期日期？如果不做类似以下可怕算法的事情,我想不出一种方法来进行计算：
>创建一个返回变量“截止日期”并将其设置为输入变量
“开始日期”
>创建一个控制变量“used minutes”并将其设置为0
>创建一个条件为“used minutes”< =“input timespan”的循环
>在循环内部,向“到期日期”返回变量添加第二个
>在循环内部,检查第二个是否在运行的几小时内
(查看营业时间,周末和假期).如果是这样,增量
控制变量“used minutes”by 1.
>退出循环后,返回变量“截止日期”
解决方法
您需要一张有效营业时间的餐桌,周末和假日不包括在内(或标记为周末/假日,因此您可以跳过它们.)每行代表一天和当天的工作小时数.然后查询从开始日期到第一个(最小)日期的营业时间表,其中总和(小时* 60)大于您的分钟参数,不包括标记的周末/假日行.这会给你结束日期.
这是日历表：
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
