user1886415 - 5 months ago 78
SQL Question

# Calculating due date using business hours and holidays

I need to calculate due date / end date for SLAs. As input values I have the start date and a timespan (in minutes). This calculation needs to take into account business hours, weekends, and holidays.

I've seen a lot of examples where the input is start date and end date, but have been struggling finding anything similar to the above input values.

Is there an elegant solution to this problem? Is there a way to calculate due date without using a loop? I can't think of a way to do the calculation without doing something similar to the following terrible algorithm:

1. Create a return variable "due date" and set it to input variable
"start date"

2. Create a control variable "used minutes" and set it to 0

3. Create a loop with the condition "used minutes" <= "input timespan"

4. Inside the loop, add a second to the "due date" return variable

5. Inside the loop, check if the second is within hours of operation
(checking business hours, weekends, and holidays). If so, increment
control variable "used minutes" by 1.

6. Upon exiting the loop, return variable "due date"

You need a table with valid business hours, with the weekends and holidays excluded (or marked as weekend/holiday so you can skip them.) Each row represents one day and the number of working hours for that day. Then you query the business hours table from your start date to the first (min) date where the sum(hours*60) is greater than your minutes parameter, excluding marked weekend/holiday rows. That gives you your end date.

Here's the day table:

``````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]
``````

here's how I populate the table with days:

``````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
``````

I also have a holiday table, but everyone's holidays are different:

``````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