February 14, 2018 at 7:05 am
Hi, I am having an issue with my below function. I want it to count the start day as 1 day which it does... but it is messing up if the start date is a holiday. It is still counting the start date as day 1 even though it should skip it and go to the next day since the start is a holiday. How do I fix this without messing it up for every other date?
IE: if I do 01/01/2018, 14 >> it tells me that 14 business days from 01/01/2018 is 01/19/2018.
If I do 01/02/2018, 14 >> It works properly and says 14 business days is 01/22/2018.
Both instances should give me 01/22/2018 as 14 business day
The Holiday table has standard holidays in it from OPM website. The date range I have here has 2 holidays. 01/01/2018 and 01/15/2018.
ALTER FUNCTION [dbo].[fn_ADD_WORKING_DAYS] (
@DATE DATE,
@NDAYS INT
) RETURNS DATE
BEGIN
IF @DATE IS NULL
BEGIN
SET @DATE = GETDATE();
END
DECLARE @STARTDATE INT = 0
DECLARE @COUNT INT = 1
DECLARE @NEWDATE DATE = DATEADD(DAY,1,@DATE)
WHILE @COUNT < @NDAYS
BEGIN
IF DATEPART(WEEKDAY, @NEWDATE) NOT IN (7, 1) AND @NEWDATE NOT IN ( SELECT DT_HOLIDAY FROM TBL_HOLIDAYS )
SET @COUNT += 1;
SELECT @NEWDATE = DATEADD(DAY, 1, @NEWDATE), @STARTDATE += 1;
END
RETURN DATEADD(DAY, @STARTDATE, @DATE);
END
CREATE TABLE [dbo].[TBL_HOLIDAYS](
[HOLIDAY_ROW_ID] [int] IDENTITY(1,1) NOT NULL,
[DT_HOLIDAY] [date] NULL
) ON [PRIMARY]
GO
February 14, 2018 at 8:38 am
The main issue here is that you are using an iterative approach instead of a set-based approach. Also, inline table valued functions are going to perform much better than scalar functions.
I've rewritten your function as an inline table-valued function. I also used a CTE to create a tally table on the fly. If you already have a tally table (or Itzik Ben Gan's tally table function) you can skip the CTE.
I've also used OFFSET/FETCH to return the desired date.
CREATE FUNCTION [dbo].[fn_ADD_WORKING_DAYS] (
@DATE DATE,
@NDAYS INT
) RETURNS TABLE WITH SCHEMABINDING
AS RETURN
WITH Tally_Base AS
(
SELECT n
FROM ( VALUES(0), (0), (0), (0), (0), (0), (0), (0),(0), (0) ) b(n)
)
, Tally AS
(
SELECT ROW_NUMBER() OVER(ORDER BY @@VERSION) n
FROM Tally_Base A
CROSS JOIN Tally_Base B
/* Add as many cross joins as necessary to get the maximum number of records you will need. */
)
/* If you already have a tally table in your database, you can skip the above CTE. */
SELECT dt
FROM Tally
CROSS APPLY( VALUES(DATEADD(DAY, n - 1, @Date))) dt(dt)
WHERE dt.dt NOT IN ( SELECT Dt_Holiday FROM Tbl_Holidays )
AND DATEPART(WEEKDAY, dt) NOT IN (1, 7)
ORDER BY dt.dt
OFFSET @NDAYS - 1 ROWS
FETCH NEXT 1 ROWS ONLY
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
February 14, 2018 at 8:50 am
Thank you, wow! I thought I was pretty good at SQL... guess not. 😉
So I can learn, what is the tally table doing exactly?
February 14, 2018 at 9:16 am
amy26 - Wednesday, February 14, 2018 8:50 AMThank you, wow! I thought I was pretty good at SQL... guess not. 😉So I can learn, what is the tally table doing exactly?
The tally table is just a list of consecutive numbers that replaces incrementing a variable in an iterative approach. You can learn more about it here: Tally OH!
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
February 14, 2018 at 1:22 pm
drew.allen - Wednesday, February 14, 2018 9:16 AMamy26 - Wednesday, February 14, 2018 8:50 AMThank you, wow! I thought I was pretty good at SQL... guess not. 😉So I can learn, what is the tally table doing exactly?
The tally table is just a list of consecutive numbers that replaces incrementing a variable in an iterative approach. You can learn more about it here: Tally OH!
Drew
Thank you so much! Always something new to learn. 🙂
February 14, 2018 at 1:31 pm
I find that it's easier to learn about Tally Tables in this article from Dwain Camps:
Tally Tables in T-SQL « dwaincsql
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply