Calculate upcoming business day from date (Updated)

/****** Object:  Table [dbo].[HolidayDate]    Script Date: 7/16/2005 6:47:26 AM ******/if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[HolidayDate]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[HolidayDate]
GO

/****** Object:  Table [dbo].[HolidayDate]    Script Date: 7/16/2005 6:47:27 AM ******/CREATE TABLE [dbo].[HolidayDate] (
[HolidayDate] [smalldatetime] NOT NULL 
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[HolidayDate] ADD 
CONSTRAINT [PK_WT_HolidayDate] PRIMARY KEY  CLUSTERED 
(
[HolidayDate]
)  ON [PRIMARY] 
GO


exec sp_addextendedproperty N'MS_Description', N'Date of a holiday', N'user', N'dbo', N'table', N'HolidayDate', N'column', N'HolidayDate'


GO



IF EXISTS (SELECT * from dbo.sysobjects 
WHERE id = object_id(N'[dbo].[CalcNextBusinessDay]') 
AND OBJECTPROPERTY(id, N'IsScalarFunction') = 1)
DROP FUNCTION [dbo].[CalcNextBusinessDay]
GO


CREATE FUNCTION dbo.CalcNextBusinessDay ( 
@dtStartingDate datetime, 
@tiDaysFromStart tinyint, 
@tiDaysInWeek tinyint = 5,
@bStartFromFollowingDay bit = 0)
RETURNS DATETIME
/**************************************************************************
DESCRIPTION: Calculates the next business day based upon the work week
length, number of days from the start date and a list of
holiday dates kept in a table called holiday date.

PARAMETERS:
@dtStartingDate- Date that calculation will start from
@tiDaysFromStart - Number of business days from start date
@tiDaysInWorkWeek- Number of days in work week. Can be 5,6, or 7.
@bStartFromFollowingDay - Indicates calculation should begin from day following start
(used to exclude today's date from being included in
situations where partially elaspsed days are not counted)

RETURNS:
Datetime of next business day calculated per input criteria

USAGE:
SELECT dbo.CalcNextBusinessDay('12/23/2005', 10, 5, 1) AS AnticipatedProcessingDate


DEPENDANCIES:
HolidayDate Table containing the dates of the holidays observed by a 
particular business.


AUTHOR:Karen Gayda

DATE: 07/12/2005

MODIFICATION HISTORY:
WHODATEDESCRIPTION
----------------------------------------------------------------
***************************************************************************/AS
BEGIN
DECLARE @dtNextBusinessDaydatetime,
@tiBusinessDaystinyint



IF @tiDaysInWeek NOT IN(5,6,7)
BEGIN
SET @dtNextBusinessDay =NULL
END
ELSE
BEGIN
SET @dtNextBusinessDay = @dtStartingDate + @bStartFromFollowingDay
SET @tiBusinessDays = 0
WHILE @tiBusinessDays < @tiDaysFromStart
BEGIN
SELECT @tiBusinessDays = CASE
WHEN DATENAME(dw,@dtNextBusinessDay) = 'Saturday' AND @tiDaysInWeek = 5
THEN @tiBusinessDays 
 
WHEN DATENAME(dw,@dtNextBusinessDay) = 'Sunday' AND @tiDaysInWeek IN(5,6)
THEN @tiBusinessDays 

WHEN EXISTS(SELECT HolidayDate FROM HolidayDate 
WHERE HolidayDate = Cast( Month(@dtNextBusinessDay) as varchar(20)) +
'/' +  Cast( Day(@dtNextBusinessDay) as varchar(20)) +
'/' + Cast( Year(@dtNextBusinessDay) as varchar(20)) )
THEN @tiBusinessDays 

ELSE
@tiBusinessDays + 1
END -- case

SELECT @dtNextBusinessDay =  CASE 
WHEN DATENAME(dw,@dtNextBusinessDay) = 'Saturday' AND @tiDaysInWeek = 5
THEN DATEADD(dd,2,@dtNextBusinessDay)
 
WHEN DATENAME(dw,@dtNextBusinessDay) = 'Sunday' AND @tiDaysInWeek IN(5,6)
THEN DATEADD(dd,1,@dtNextBusinessDay)
ELSE
DATEADD(dd,1,@dtNextBusinessDay)
END -- case

END --while
END --else

RETURN (@dtnextBusinessDay)
END

GO