Working days on SQL Server using CTE in function
I am trying to write a function to return the number of business days between two dates. I have a table with all bank holidays that has two fields, BHID and BHDate.
I wrote the following SELECT query to test my theory and it works nicely:
DECLARE @StartDate AS Date, @EndDate AS Date
SET @StartDate = '2015-01-01'
SET @EndDate = '2015-07-01'
;WITH CTE AS (
SELECT @StartDate AS StartDate, @EndDate AS EndDate, @StartDate AS DateCalc
UNION ALL
SELECT @StartDate AS StartDate, @EndDate AS EndDate, DATEADD(dd, 1, DateCalc) AS DateCalc FROM CTE
WHERE DATEADD(dd, 1, DateCalc) <= EndDate)
SELECT COUNT(*) AS Days
FROM CTE
LEFT JOIN psd.dbo.LUBankHolidays BH
ON CTE.DateCalc = BH.BHDate
WHERE BHID IS NULL
AND LEFT(DATENAME(dw, DateCalc) ,1) <> 'S'
option (Maxrecursion 0);
But when I try to put it in a function, I get some errors around the SET line, it could be I just am not very good at functions like the rest of my knowledge, or I was wondering if it should be done with a CTE not used before the line SET? Here's my attempt:
CREATE FUNCTION [dbo].[WorkingDays]
(@StartDate AS Date, @EndDate AS Date
)
RETURNS INT
AS
BEGIN
DECLARE @Days AS INT
;WITH CTE AS (
SELECT @StartDate AS StartDate, @EndDate AS EndDate, @StartDate AS DateCalc
UNION ALL
SELECT @StartDate AS StartDate, @EndDate AS EndDate, DATEADD(dd, 1, DateCalc) AS DateCalc FROM CTE
WHERE DATEADD(dd, 1, DateCalc) <= EndDate)
SET @Days =
(SELECT COUNT(*) AS Days
FROM CTE
LEFT JOIN psd.dbo.LUBankHolidays BH
ON CTE.DateCalc = BH.BHDate
WHERE BHID IS NULL
AND LEFT(DATENAME(dw, DateCalc) ,1) <> 'S'
option (Maxrecursion 0);)
RETURN @Days
END
I even tried to insert the INT result into a temporary table and then reset it after setting @Days before it returns @Days, but that would throw an error related to not being allowed to create temporary tables as part of the function.
Any help would be great, I'm sure this is a small thing, but just eludes me at the moment.
source to share
Since CTE can only be used in a query, instead of using it SET
to set a variable, use SELECT
instead:
SELECT @Days =
(SELECT COUNT(*) AS Days
FROM CTE …
From MSDN :
The CTE must be followed by a single SELECT, INSERT, UPDATE or DELETE that refers to some or all of the CTE columns.
source to share
Worked with stuartd help:
CREATE FUNCTION [dbo].[WorkingDays]
(@StartDate AS Date, @EndDate AS Date
)
RETURNS INT
AS
BEGIN
DECLARE @Days AS INT
;WITH CTE AS (
SELECT @StartDate AS StartDate, @EndDate AS EndDate, @StartDate AS DateCalc
UNION ALL
SELECT @StartDate AS StartDate, @EndDate AS EndDate, DATEADD(dd, 1, DateCalc) AS DateCalc FROM CTE
WHERE DATEADD(dd, 1, DateCalc) <= EndDate)
SELECT @Days =
(SELECT COUNT(*) AS Days
FROM CTE
LEFT JOIN psd.dbo.LUBankHolidays BH
ON CTE.DateCalc = BH.BHDate
WHERE BHID IS NULL
AND LEFT(DATENAME(dw, DateCalc) ,1) <> 'S')
option (Maxrecursion 0);
RETURN @Days
END
Thanks everyone
source to share
Use this function
CREATE FUNCTION [dbo].[WorkingDays]
(@Start_Date AS Date, @end_Date AS Date
)
RETURNS INT
AS
BEGIN
DECLARE @Days AS INT
WITH AllDays
AS ( SELECT @start_date AS [Date], 1 AS [level]
UNION ALL
SELECT DATEADD(DAY, 1, [Date]), [level] + 1
FROM AllDays
WHERE [Date] < @end_date )
SELECT @Days =COUNT(*) AS Days
FROM AllDays
LEFT JOIN psd.dbo.LUBankHolidays BH
ON AllDays.Date= BH.BHDate
WHERE BHID IS NULL
AND LEFT(DATENAME(dw, AllDays.Date) ,1) <> 'S'
RETURN @Days
END
source to share