Monday, August 3, 2009

Find the next business days

 

Calculate the next x business day (excluding Sat, Sun). Basically a businessdateadd() for dateadd()

IF     EXISTS (SELECT * FROM sysobjects WHERE xtype = 'FN' AND name = 'fn_next_business_day')
BEGIN
DROP FUNCTION fn_next_business_day
END
go

CREATE FUNCTION fn_next_business_day
(
@start_date datetime,
@days int
)
RETURNS datetime
AS
BEGIN
DECLARE @wd int

-- get the weekday AND CONVERT to datefirst = 1 value
SELECT @wd = ((DATEPART(weekday, @start_date) - 1 + 7 - (8 - @@datefirst)) % 7) + 1

-- IF it IS Sat, Sun, change teh date to Next Monday
IF @wd IN (6, 7)
BEGIN
SELECT @start_date = DATEADD(DAY, 7 - @wd + 1, @start_date)
SELECT @wd = ((DATEPART(weekday, @start_date) - 1 + 7 - (8 - @@datefirst)) % 7) + 1
END

RETURN
(
SELECT DATEADD(DAY,
@days + CASE WHEN @days >= (5 - @wd + 1)
THEN ((@days + ((DATEPART(weekday, @start_date) - 1 + 7 - (8 - @@datefirst)) % 7) + 1 - 1)/ 5) * 2
ELSE 0
END,
@start_date)
)
END

No comments:

Post a Comment