CREATE TABLE [holiday] (
[holidayDate] [smalldatetime] NOT NULL ,
CONSTRAINT [PK_holidayDate] PRIMARY KEY CLUSTERED
(
[holidayDate]
)
)
create function fnGetNextBusinessDay (@startDate smalldatetime,@numDays int)
returns smalldatetime as
Begin
Declare @nextBusDay smalldatetime
Declare @weekDay tinyInt
set @nextBusDay = @startDate
Declare @dayLoop int
set @dayLoop = 0
while @dayLoop < @numDays
Begin
set @nextBusDay = dateAdd(d,1,@nextBusDay) -- first get the raw next day
SET @weekDay =((@@dateFirst+datePart(dw,@nextBusDay)-2) % 7) + 1
-- always returns Mon=1 - can't use set datefirst in UDF
-- % is the Modulo operator which gives the remainder
-- of the dividend divided by the divisor (7)
-- this allows you to create repeating
-- sequences of numbers which go from 0 to 6
-- the -2 and +1 adjust the sequence start point (Monday) and initial value (1)
if @weekDay = 6 set @nextBusDay = @nextBusDay + 2 -- since day by day Saturday = jump to Monday
-- Holidays - function calls itself to find the next business day
select @nextBusDay = dbo.fnGetNextBusinessDay(@nextBusDay,1)
where exists (select holidayDate from Holiday where holidayDate=@nextBusDay)
-- next day
set @dayLoop = @dayLoop + 1
End
return @nextBusDay
End
