Monday, 23 June 2014

Removing weekends and Bank Holidays (Dubai) from TSQL SELECT with a function


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: