Calculating due date using business hours and holidays
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
This is the best I could do, still uses a loop but uses date functions instead of incrementing a minutes variable. Hope you 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))),''))
end
select @due_date
Sql to Calculate due date excluding holidays and considering business hour as below :- < note : - It's working correctly Business hour (8-5) Maintain holiday table
CREATE TABLE [dbo].[holiday](
[id] [int] IDENTITY(1,1) NOT NULL,
[region] [nvarchar](10) NULL,
[Hdate] [date] NULL,
)
>
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