Add working days but avoid holidays

, 2019-05-01

Very often there is a need to generate a date by adding (or subtracting) a number of working days. A process might have a lead time of N days and so a date which is N working days after another is required. Or perhaps an activity has to happen on a given date and a pre-activity date which is M working days before that has to be generated.

Straightforward enough if only weekends have to be considered - but what happens when statutory holidays or other non-working days get in the way?

The following Scalar Valued Function allows the user to generate days which are a number of working days either before or after a given date. It uses a table called tblHoliday in which each record represents a single non-working day. The table has an ID field called HolidayID and the date of the non-working day is held in a field called HolidayDate.

The Function will work however day-of-the-week-numbering has been set up since it finds the DW (day of the Week) values for Saturday and Sunday before performing a simple step-through-and-count loop which hops over weekends and holidays. This can easily be altered for countries where weekends are on other days of the week.

CREATE FUNCTION [dbo].[fns_AddWorkingDays] 
(
	@StartDate datetime,
	@N INT
)
RETURNS datetime 
AS 
BEGIN
  -- This ensures that however the server is configured for dates
  -- the function will know the DATEPART(DW values for Saturday
  -- and Sunday
  declare @SaturdayDW int
  declare @SundayDW int
  set @SaturdayDW = DATEPART(DW,CONVERT(datetime,'2019 January 5')) -- A Saturday
  set @SundayDW = DATEPART(DW,CONVERT(datetime,'2019 January 6')) -- A Sunday
  -----------------------------------------------------------------
  -- If @N is zero then reduce the date by 1
  -- and try adding one day
  if @N=0
    begin
	  set @N=1
	  set @StartDate=DATEADD(DAY,-1,@StartDate)
	end
  ----------------------------------------------------------------
  -- If @N GTE 0 then increment dates while counting
  -- If @N LT 0 then decrement dates while counting
  declare @increment int
  if @n>=0 set @increment = 1 else set @increment = -1
  -- Work out what to do if the loop encounters
  -- a Saturday or Sunday - it depends on the
  -- direction of travel.
  declare @saturdayadjustment int
  declare @sundayadjustment int
  if @n>=0 set @saturdayAdjustment = 2 else set @saturdayAdjustment = -1
  if @n>=0 set @sundayAdjustment = 1 else set @sundayAdjustment = -2
  ----------------------------------------------------------------
  declare @CountDays int
  set @CountDays=0
  declare @LoopDate datetime
  set @LoopDate = @StartDate
  while @CountDays<ABS(@N)
    begin
      set @LoopDate=DATEADD(DAY,@increment,@LoopDate)
      if DATEPART(DW,@LoopDate)= @SaturdayDW
         begin
            set @LoopDate=DATEADD(DAY,@saturdayAdjustment,@LoopDate)
         end
      if DATEPART(DW,@LoopDate)= @SundayDW
         begin
            set @LoopDate=DATEADD(DAY,@SundayAdjustment,@LoopDate)
         end
      if exists(select HolidayID from tblHoliday where HolidayDate=@LoopDate)
         begin
            set @LoopDate=DATEADD(DAY,@increment,@LoopDate)
         end
      set @CountDays=@CountDays+1
    end
  return @LoopDate

END

Rate

2 (1)

Share

Share

Rate

2 (1)

Related content