Alter FUNCTION dbo.fn_WorkDays (@StartDate AS DATETIME, @EndDate AS DATETIME)
--Define the output data type.
RETURNS INT
AS
--Calculate the RETURN of the function.
BEGIN
RETURN (
SELECT
(DATEDIFF(dd,@StartDate, @EndDate)+1)--Start with total number of days including weekends +1 Includes the day run
-(DATEDIFF(wk,@StartDate, @EndDate)*2)--Subtact 2 days for each full weekend
-(CASE WHEN DATENAME(dw, @StartDate) = 'Friday' --If StartDate is a Sunday, Subtract 1
THEN 1
ELSE 0
END)
-(CASE WHEN DATENAME(dw, @EndDate) = 'Saturday'--If EndDate is a Saturday, Subtract 1
THEN 1
ELSE 0
END)
--Now check if there are any bank holidays between the start and end and remove that amount
- (SELECT COUNT(ivm.HolidayDate)
FROM Holiday ivm
WHERE ivm.HolidayDate BETWEEN @StartDate AND @EndDate
AND (DatePart(dw, HolidayDate) != 1) and (DatePart(dw, HolidayDate) != 7))
--WHERE ivm.[Date] BETWEEN '2006-04-14' AND '2006-05-29')
)
END
GO
No comments:
Post a Comment