user1886415 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"


Answer

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

Comments