SQL Server 2005 - Date Advancement

Found a cool use for MSSQL 2005 - Common Table Expressions. I wrote this function utilizing recursion to add the number of days you specify skipping weekends. I am sharing this because I've seen a lot of these functions done poorly. This one works well-- no flaws discovered.



/****** Object: UserDefinedFunction [ufnAdvanceWeekDays] Script Date: 07/09/2010 10:20:10 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[ufnAdvanceWeekDays]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION dbo.[ufnAdvanceWeekDays]

GO

/*
Summary
This function will increment or decerement the @advanceFromDateTime by @numberOfDaysToAdvance skipping weekends.
For example if @advanceFromDateTime is a Saturday or Sunday, and @numberOfDaysToAdvance is 1 then
the following Tuesday date will be returned, or previous Thursday if @numberOfDaysToAdvance is -1.
*/
create function dbo.ufnAdvanceWeekDays(@advanceFromDateTime datetime, @numberOfDaysToAdvance as int)
Returns datetime
AS
BEGIN

/*
declare @advanceFromDateTime datetime
declare @numberOfDaysToAdvance as int

set @advanceFromDateTime=getdate()+1
set @numberOfDaysToAdvance=1

select dbo.ufnAdvanceWeekDays(@advanceFromDateTime,@numberOfDaysToAdvance)

----769 calculations per second with 50 @numberOfDaysToAdvance
----78 per second with 500 @numberOfDaysToAdvance
----by the above results a close proportion can be made between @numberOfDaysToAdvance and calculations per second.
*/

declare @advancedDate datetime
,@SATURDAY as int
,@SUNDAY as int
,@advancement as int

set @advancedDate = @advanceFromDateTime
set @SATURDAY = 7
set @SUNDAY = 1

IF(@numberOfDaysToAdvance!=0)
BEGIN

if(@numberOfDaysToAdvance<0)
set @advancement=-1
ELSE
set @advancement=1

;WITH dateRecursionCTE([advanceDate],[isWeekend],numberOfDaysRemaining)
AS
(
---- Anchor Member
SELECT
@advanceFromDateTime advanceDate
, case when datepart(dw,@advanceFromDateTime) in (@SATURDAY,@SUNDAY) then 1 else 0 end isWeekend
, abs(@numberOfDaysToAdvance) numberOfDaysRemaining
UNION ALL
---- Recursive Member
select
_tmpCTEResults.advanceDate
,_tmpCTEResults.isWeekend
-- only decrement recursive terminator for weekdays
,(_tmpCTEResults.numberOfDaysRemaining-(abs(_tmpCTEResults.isWeekend-1))) numberOfDaysRemaining
from
(
SELECT
cte.advanceDate+@advancement advanceDate
, case when datepart(dw,cte.advanceDate+@advancement) in (@SATURDAY,@SUNDAY) then 1 else 0 end isWeekend
, numberOfDaysRemaining
from dateRecursionCTE cte
where numberOfDaysRemaining!=0
) _tmpCTEResults
)

--select * from dateRecursionCTE OPTION (MAXRECURSION 32767)

select @advancedDate =
(
select
@advanceFromDateTime+
((@advancement*sum(isWeekend))+@numberOfDaysToAdvance)
--advance date by requested days + weekends
from dateRecursionCTE
)
OPTION (MAXRECURSION 32767)
----Limited by an approximate 89 year date advancement

END

--select @advancedDate
return @advancedDate
END

GO

--GRANT EXEC ON dbo.[ufnAdvanceWeekDays]  TO someone