CODEWITHSUNDEEP
t-sql
MoiD101
MoiD101

Reputation: 195

Return number of working days between 2 dates minus weekend and bank holidays

Hi i am trying to script a TSQL function that will return the number of working days between 2 dates minus weekend days and UK public holidays, here is what i have so far (due to lack of experience there may be syntax errors etc, appreciate any corrections):

CREATE 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)-(DATEDIFF(wk,@StartDate, @EndDate)*2)-(CASE WHEN DATENAME(dw, @StartDate) = 'Sunday'THEN 1 ELSE 0 END)-(CASE WHEN DATENAME(dw, @EndDate) = 'Saturday' THEN 1 ELSE 0 END)
        )END
GO

Now i have access to a table (or i can copy it if need be) that stored the UK bank holidays here is a screeny of setup:

Sceeny of table setup

im fairly new to TSQL and i am struggling with a way to look through the list of bankhols and remove them from the returning INT of the function and therefore return the correct number of working days between the 2 dates passed to the function.

any help and advice would be greatly appreciated (especially if its written in an easy to understand form ;) )

Upvotes: 0

Views: 810

Answers (1)

KekuSemau
KekuSemau

Reputation: 6856

I have been using a Calendar table for things like this - one entry for every date, and metadata like weekdays, weekends and holidays for various contexts. It is very handy for a lot of tasks. (You can set it up with a simple WHILE loop.)

The counting may look like this:
(calendarWeekday would be filled with DATEPART(Weekday... upon generation, results vary with region).

SELECT COUNT(calendarDate)
FROM tblCalendar
WHERE 
    calendarDate BETWEEN @StartDate AND @Enddate
    AND calendarWeekday NOT IN (1, 7) AND calendarIsBankHoliday = 0

Upvotes: 2

Related Questions