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

Question

I have a TSQL function that i think(im not strong with SQL syntax) will remove weekends when called:

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) = 'Sunday' --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))       
END

But i would also like to take away any existing bank holidays that i can get from a table using similar code to this:

  SELECT COUNT([Date])
  FROM [InvoiceManagement].[dbo].[tblBankHolidays]
  WHERE [Date] BETWEEN '2006-04-14' AND '2006-05-29'--eventually replace dates with @StartDate, @EndDate

Is it possible to stitch the above select into the function so that it will minus any existing bank hols from the result before returning the INT? if so i would be appreciate a walk through on how as i am not strong in TSQL

You can perhaps use TSQL Window functions to determine the holidays in your code.. see http://stackoverflow.com/a/22050586/58620 — Rob McCauley, Feb 22 '17 at 20:22

Bogdan Sahlean · Answer 1 · 2013-08-01T17:15:48.390

3

SQL Server 2008:

First, I would use a table for holidays and I would insert all holidays (#Saturdays & Sundays):

CREATE TABLE dbo.Holiday(HolidayDate DATE PRIMARY KEY);
GO
INSERT INTO dbo.Holiday(HolidayDate) VALUES ('2013-07-06'); -- Saturday
GO
INSERT INTO dbo.Holiday(HolidayDate) VALUES ('2013-07-07'); -- Sunday
GO
...

and then, to get working days between two dates, I would use this query:

DECLARE @StartDate DATE,@EndDate DATE;
SELECT  @StartDate='2013-07-01',
    @EndDate='2013-07-31';

SELECT  DATEDIFF(DAY,@StartDate,@EndDate) + 1 - COUNT(*) AS WorkingDaysCount
FROM    dbo.Holyday h
WHERE   h.HolidayDate BETWEEN @StartDate AND @EndDate;

Note: DATENAME isn't deterministic:

SET LANGUAGE english;
SELECT DATENAME(dw, '2013-08-01') AS DateNm_EN;
GO
SET LANGUAGE romanian;
SELECT DATENAME(dw, '2013-08-01') AS DateNm_RO;
GO

Results:

DateNm_EN
---------
Thursday

DateNm_RO
---------
joi

Edit 1:

USE [InvoiceManagement];
GO
CREATE FUNCTION dbo.fn_WorkDays_v2 (@StartDate AS DATE, @EndDate AS DATE) -- Arguments should have the same type as column's type
RETURNS INT
AS
BEGIN
    DECLARE @HolidaysCount INT;

    SELECT @HolidaysCount=COUNT(*)
    FROM [dbo].[tblBankHolidays] h
    WHERE h.[Date] BETWEEN @StartDate AND @EndDate;

    DECLARE @WeekendDaysCount INT;
    WITH N10(Num)
    AS
    (
        SELECT Num FROM (VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9),(10))  n(Num)
    ), N100(Num)
    AS
    (
        SELECT (a.Num-1)*10 + b.Num AS Num
        FROM N10 a CROSS JOIN N10 b
    ), N10000(Num)
    AS
    (
        SELECT (a.Num-1)*100 + b.Num AS Num
        FROM N100 a CROSS JOIN N100 b
    )
    SELECT @WeekendDaysCount=COUNT(*)
    FROM N10000 n
    WHERE DATEDIFF(DAY,@StartDate,@EndDate) >= n.Num
    AND DATEDIFF(DAY,0, DATEADD(DAY,n.Num-1,@StartDate)) % 7 IN (5,6); -- 5=Saturday, 6=Sunday

    RETURN (DATEDIFF(DAY,@StartDate, @EndDate)+1 - @HolidaysCount - @WeekendDaysCount);       
END

edited Aug 01 '13 at 17:15

answered Aug 01 '13 at 10:23

Bogdan Sahlean

19,233
3
42
57

Thanks @Bogdan i think i was not as clear as i could of been perhaps. I have access to the [InvoiceManagement].[dbo].[tblBankHolidays] table as it exists. The Select query does what i want it to and produces an INT when date criteria is met. Its the syntax that im struggling with in order to minus that figure (if it exists) from the result in the existing function. IE no of days(from the function) - no of bank holiday days from the Select script = total working days. – MoiD101 Aug 01 '13 at 11:49
@MoiD101: Add the structure of this table, please. There are others columns except `[Date]` column ? Do you use this table to store Saturdays & Sundays or, maybe, this table is used to store all days and you have a `BIT` column `IsHoliday` ? – Bogdan Sahlean Aug 01 '13 at 11:53
There are other columns in the tbbankholidays table, but they have no use the date field contains all of the dates of the bank holidays EG 2013-12-25 the function as is (above) should work out the number of days between the dates and strip out weekend days. The idea then is to then take away any days from that answer that are found when the Select script result is run. The problem is incorporating the select script into the function in some way. – MoiD101 Aug 01 '13 at 12:10
@MoiD101: See **Edit 1** – Bogdan Sahlean Aug 01 '13 at 12:57
1

Thank you for taking the time out to help me, i found/find it very fifficault to follow your solution, but i get the rough idea behind it (just my lack f experience sorry). I have however added what i think is fitting my requirements and seems to yield the results when checked (the correct number of working days minus the weekend and bank holidays). I am going to get the code reviewed an additional precaution. – MoiD101 Aug 01 '13 at 16:44
1

Tell me what's difficult ? `WITH N10(Num) .... N10000(Num)...` ? This Common Table Expression is nothing more than a replacement for a tables with all numbers from 1 to 10000. And `SELECT @WeekendDaysCount=COUNT(*) ... ` counts how many weekends are between those two dates. This last SELECT could be improved. But, the simplest solution will be to have a table with all holidays including weekends (first solution). – Bogdan Sahlean Aug 01 '13 at 17:09

score 0 · Answer 2 · answered Aug 01 '13 at 16:41

I believe i have found an alternative working solution; a simpler solution (to me anyway) than the one above; i thinking the above is a bit hard for me to read and understand sorry @Bogdan. Here is the script that is making use of the function and seems to show the correct results, when i check them against the calender:

SELECT 
cir.[PW Number] AS PWNum
--Get the correct number of working dayts since the order date by using the fn_WorkDays function
,CONVERT(VARCHAR(10),singleended2.dbo.fn_WorkDays(cir.[Install Date], GETDATE()),103) AS NumberOfDaysSinceOrderDate 
,CONVERT(VARCHAR(10), ISNULL(cir.[Install Date],'01/01/1900'),103) AS  OrderDate--Get the order dates
,ISNULL(cirRep.CurrentStage, 'Not Set') AS CurrentStage
,cir.[ID] as CircuitID
FROM Quotebase.dbo.Circuits cir
    LEFT JOIN Quotebase.dbo.CircuitReports cirRep ON Cir.[PW Number] = CirRep.PWNumber
WHERE Cir.Status='New Circuit Order' 
ORDER BY Cir.[PW Number]

Here is the function script that is called:

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) = 'Sunday' --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.[Date])
          FROM [EUROPEVUK386].[InvoiceManagement].[dbo].[tblBankHolidays] ivm
          WHERE ivm.[Date] BETWEEN @StartDate AND @EndDate)     
          --WHERE ivm.[Date] BETWEEN '2006-04-14' AND '2006-05-29')     
)
 END
GO

Ill probably go and get the code reviewed and edit this if it is indeed incorrect.

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

2 Answers2