Date calculation using business hours and holidays

Question

I need to calculate the end date / end date of the SLA. As input values, I have a start date and time (in minutes). This calculation should take into account working hours, weekends and holidays.

I saw many examples where input is the start date and end date, but they tried to find something similar to the above values.

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

• Create a return variable "due date" and set the input variable "start date" for it
• Create the "used minutes" control variable and set it to 0
• Create a loop with the condition "used minutes" <= "input time"
• Inside the loop add the second term variable "expiration"
• Inside the cycle, check if the second second of work is working (checking working hours, weekends and holidays). If so, increment the control variable "Used minutes" by 1.
• After exiting the loop, return the variable "date of completion"

Answer 1

You need a table with current business hours, excluding weekends and holidays (or marked as weekends / holidays so you can skip them). Each line represents one day and the number of working hours for that day. Then you request a table of working hours from the start date to the first (minimum) date when the amount (hours * 60) is greater than your minutes parameter, except for the marked weekend / holiday lines. This gives you an end date.

Here's the daily chart:

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, as I fill in the table for 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 all the holidays are different:

 holidayID holiday rule description 1 New Year 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 

NTN

Answer 2

This is the best I could do, still use a loop, but use date functions instead of increasing the minutes variable. I hope you will like it.

 --set up our source data declare @business_hours table ( work_day varchar(10), open_time varchar(8), close_time varchar(8) ) insert into @business_hours values ('Monday', '08:30:00', '17:00:00') insert into @business_hours values ('Tuesday', '08:30:00', '17:00:00') insert into @business_hours values ('Wednesday', '08:30:00', '17:00:00') insert into @business_hours values ('Thursday', '08:30:00', '17:00:00') insert into @business_hours values ('Friday', '08:30:00', '18:00:00') insert into @business_hours values ('Saturday', '09:00:00', '14:00:00') 

declare @holidays table ( holiday varchar(10) ) insert into @holidays values ('2015-01-01') insert into @holidays values ('2015-01-02')

--Im going to assume the SLA of 2 standard business days (0900-1700) = 8*60*2 = 960 declare @start_date datetime = '2014-12-31 16:12:47' declare @time_span int = 960-- time till due in minutes

declare @true bit = 'true' declare @false bit = 'false'

declare @due_date datetime --our output

--other variables declare @date_string varchar(10) declare @today_closing datetime declare @is_workday bit = @true declare @is_holiday bit = @false

--Given our timespan is in minutes, lets also assume we dont care about seconds in start or due dates set @start_date = DATEADD(ss,datepart(ss,@start_date)*-1,@start_date)

while (@time_span > 0) begin

 set @due_date = DATEADD(MINUTE,@time_span,@start_date) set @date_string = FORMAT(DATEADD(dd, 0, DATEDIFF(dd, 0, @start_date)),'yyyy-MM-dd') set @today_closing = (select convert(datetime,@date_string + ' ' + close_time) from @business_hours where work_day = DATENAME(weekday,@start_date)) if exists((select work_day from @business_hours where work_day = DATENAME(weekday,@start_date))) set @is_workday = @true else set @is_workday = @false if exists(select holiday from @holidays where holiday = @date_string) set @is_holiday = @true else set @is_holiday = @false if @is_workday = @true and @is_holiday = @false begin if @due_date > @today_closing set @time_span = @time_span - datediff(MINUTE, @start_date, @today_closing) else set @time_span = @time_span - datediff(minute, @start_date, @due_date) end set @date_string = FORMAT(DATEADD(dd, 1, DATEDIFF(dd, 0, @start_date)),'yyyy-MM-dd') set @start_date = CONVERT(datetime, @date_string + ' ' + isnull((select open_time from @business_hours where work_day = DATENAME(weekday,convert(datetime,@date_string))),'')) 

@due_date

code>

Answer 3

Here is an option using the WorkSchedule table, which will contain the working hours available for counting towards the SLA. To account for weekends and holidays, simply do not insert entries on these days in the WorkSchedule table.

This solution also uses the “Tally” table in the number table per date. I also included debugging output to help you understand what is going on, so just comment out or uncomment the debug sections to see less / more information.

I used SQL temp tables in this example so that you can run it without spoiling the existing database schema, but when using this solution you should replace it with physical tables.

Test data settings:

 CREATE TABLE #WorkSchedule(WorkStart datetime not null primary key, WorkEnd datetime not null); GO CREATE TABLE #Tally (N int not null primary key); GO --POPULATE TEST DATA --populate Tally table insert into #Tally (N) select top 10000 N = row_number() over(order by o.object_id) from sys.objects o cross apply sys.objects o2 ; go --POPULATE WITH DUMMY TEST DATA INSERT INTO #WorkSchedule(WorkStart, WorkEnd) SELECT workStart = dateadd(hour, 8, t.workDate) , workEnd = dateadd(hour, 17, t.workDate) FROM ( SELECT top 10000 workDate = dateadd(day, row_number() over(order by o.object_id), '2000-01-01') FROM sys.objects o cross apply sys.objects o2 ) t --Exclude weekends from work schedule WHERE datename(weekday, t.workDate) not in ('Saturday','Sunday') ; GO 

Code for calculating the due date:

 SET NOCOUNT ON; DECLARE @startDate datetime; DECLARE @SLA_timespan_mins int; DECLARE @workStartDayOne datetime; DECLARE @SLA_Adjusted int; DECLARE @dueDate datetime; --SET PARAM VALUES HERE FOR TESTING TO ANY DATE/SLA TIMESPAN YOU WANT: SET @startDate = '2014-01-04 05:00'; --Saturday SET @SLA_timespan_mins = 10 * 60 ; --10 hrs. --get the info day 1, since your start date might be after the work start time. select top 1 @workStartDayOne = s.WorkStart --increase the SLA timespan mins to account for difference between work start and start time , @SLA_Adjusted = case when @startDate > s.WorkStart then datediff(minute, s.WorkStart, @startDate) else 0 end + @SLA_timespan_mins from #WorkSchedule s where s.WorkEnd > @startDate and s.WorkStart <> s.WorkEnd order by s.WorkStart asc ; --DEBUG info: select 'Debug Info' as DebugInfo, @startDate AS StartDate, @workStartDayOne as workStartDayOne, @SLA_timespan_mins as SLA_timespan_mins, @SLA_Adjusted as SLA_Adjusted; --now sum all the non work hours during that period and determine the additional mins that need added. ;with cteWorkMins as ( SELECT TOP (@SLA_Adjusted) s.WorkStart, s.WorkEnd , WorkMinute = dateadd(minute, tN, cast(s.WorkStart as datetime)) , tN as MinuteOfWorkDay , RowNum = row_number() over(order by s.WorkStart, tN) FROM #WorkSchedule s INNER JOIN #Tally t ON tN between 1 and datediff(minute, s.WorkStart, s.WorkEnd) WHERE s.WorkStart >= @workStartDayOne ORDER BY s.WorkStart, tN ) /**/ SELECT @dueDate = m.WorkMinute FROM cteWorkMins m WHERE m.RowNum = @SLA_Adjusted --*/ /** --DEBUG: this query will show every minute that is accounted for during the Due Date calculation. SELECT m.* FROM cteWorkMins m --WHERE m.RowNum = @SLA_Adjusted ORDER BY m.WorkMinute --*/ ; select @dueDate as DueDate; GO 

Cleaning Check:

 IF object_id('TEMPDB..#WorkSchedule') IS NOT NULL DROP TABLE #WorkSchedule; GO IF object_id('TEMPDB..#Tally') IS NOT NULL DROP TABLE #Tally; 

GO

Answer 4

as I understood from your question what you need, follow

• You have indicated the start date and the number of minutes added to it, then you need to get the set date.
• To get the deadline, you must exclude the holidays, and the deadline should be during the working day.

here is what you can do

 declare @start datetime, @min int, @days int set @start= '28 Dec 2014' set @min = 2200 -- get the number of days set @days=datediff(day,@start,dateadd(minute,@min,@start)) -- get the due date select max(Date) from (select row_number() over( order by t.Id)-1 as Id,t.Date from DateMetadata t inner join BusinessDays b on Day(t.Date) = b.Day where t.Date > = @start and not exists(select 1 from Holidays h where h.Day=Day(t.Date) and h.Month=Month(t.Date))) as p where p.Id < @days 

Note : you will install this DateMetadata table in your database once.

setting for the above code:

 create table Holidays(Id int identity(1,1), Name nvarchar(50), Day int, Month int) create table BusinessDays(Id int identity(1,1), Name nvarchar(20), Day int) -- i am putting some days that are known, -- it depends on you to define which holidays you want insert into Holidays (Name,Day,Month) values('Christmas',25,12) insert into Holidays(Name,Day,Month) values('New Year',31,12) insert into Holidays(Name,Day,Month) values('Valentine',14,2) insert into Holidays(Name,Day,Month) values('Mothers day',21,3) insert into Holidays(Name,Day,Month) values('April fools day',1,4) -- i am assuming that the business days are from monday till friday and -- saturday and sunday are off days insert into BusinessDays(Name,Day) values ('Monday',1) insert into BusinessDays(Name,Day) values('Tuesday',2) insert into BusinessDays(Name,Day) values('Wednesday',3) insert into BusinessDays(Name,Day) values('Thursday',4) insert into BusinessDays(Name,Day) values('Friday',5) 

this table is necessary and you will install it once

 -- set up a table that contains all dates from now till 2050 for example -- and you can change the value of 2050 depending on your needs -- this table you will setup it once create table DateMetadata(Id int identity(1,1), Date datetime) declare @date datetime set @date='01 Jan 2014' while @date < '31 Dec 2050' begin insert into DateMetadata(Date) values(@date) set @date = @date + 1 end 

DEMO works here

if you need any explanation I'm ready

hope this helps you

Answer 5

Sql for calculating the period, excluding holidays and taking into account the working hours, as shown below: - <Note: - It works correctly. Working hours (8-5). Maintaining a festive table.

 CREATE TABLE [dbo].[holiday]( [id] [int] IDENTITY(1,1) NOT NULL, [region] [nvarchar](10) NULL, [Hdate] [date] NULL, ) 

<P →

 declare @start datetime= getdate() declare @slamins int =960 --- SLA time in mins declare @Country varchar(2)='NA' declare @start_hour int = 8 -- business start hour declare @end_hour int = 17 -- business end hour declare @true bit = 'true' declare @false bit = 'false' declare @is_workday bit = @true declare @is_holiday bit = @false declare @due_date datetime declare @today_closing datetime declare @temp int = 0 declare @holidays table (HDate DateTime) -- Table variable to hold holidayes ---- Get country holidays from table based on the country code (Feel free to remove this or modify as per your DB schema) Insert Into @Holidays (HDate) Select date from HOLIDAY Where region=@Country and Hdate>=DateAdd(dd, DateDiff(dd,0,@start), 0) --check for weekends set @start = case(datepart(dw,@start) +@ @datefirst-1)%7 when 0 then Convert(dateTime,CONVERT(varchar,@start+1,101)+' 08:00:00') when 6 then Convert(dateTime,CONVERT(varchar,@start+2,101)+' 08:00:00') else @start end -- check if start time is before business hour if datepart(hh, @start) < @start_hour set @start = Convert(dateTime,CONVERT(varchar,@start,101)+' 08:00:00') -- check if start time is after business hour if datepart(hh, @start) >= @end_hour set @start = Convert(dateTime,CONVERT(varchar,@start+1,101)+' 08:00:00') -- loop start while (@slamins > 0) begin -- prepared closing date time based on start date set @today_closing = Convert(dateTime,CONVERT(varchar,@start,101)+' 17:00:00') set @due_date = @start -- calculate number of Minute between start date and closing date set @temp = DATEDIFF(N, @start , @today_closing); --check for weekends if (DATEPART(dw, @start)!=1 AND DATEPART(dw, @start)!=7) set @is_workday = @true else set @is_workday = @false --check for holidays if (Select Count(*) From @Holidays Where HDATE=DateAdd(dd, DateDiff(dd,0,@start), 0)) = 0 set @is_holiday =@false else set @is_holiday = @true if @is_workday = @true and @is_holiday = @false begin if(@temp < @slamins) begin set @slamins = @slamins - @temp end else begin set @due_date = DATEADD(MINUTE,@slamins,@start) set @slamins = 0 print @due_date end end set @start = Convert(dateTime,CONVERT(varchar,@start+1,101)+' 08:00:00') end select @due_date