Add working days but avoid holidays

Question

Add working days but avoid holidays

steve 14359

SSC Enthusiast

Points: 182
More actions
June 26, 2019 at 6:59 pm

#3439974

Comments posted to this topic are about the item Add working days but avoid holidays

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

Thom A SSC Guru Points: 99126 More actions · Answer 1

Just wondering here, what was your reasoning for using a WHILE loop here? Personally I would see that you would get better performance by using a Tally Table and/or Calendar table.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

sterling3721 SSChasing Mays Points: 653 More actions · Answer 2

Thank you for sharing. I am looking for something like this. When I tried your script, it complained @increment was not declared. Please update the script.

steve 14359 SSC Enthusiast Points: 182 More actions · Answer 3

My original script contained a comment line with a "Less Than" sign which was interpreted as a opening html tag when uploaded!!

So I've altered the script but here are the offending lines after correction:-

----------------------------------------------------------------

-- If @N GTE 0 then increment dates while counting

-- If @N LT 0 then decrement dates while counting

declare @increment int

if @n>=0 set @increment = 1 else set @increment = -1

-- Work out what to do if the loop encounters

-- a Saturday or Sunday - it depends on the

-- direction of travel.

declare @saturdayadjustment int

declare @sundayadjustment int

steve 14359 SSC Enthusiast Points: 182 More actions · Answer 4

Thom A, I could imagine your suggestion might result in better performance if large offsets are added (or subtracted) from a date. However the function was designed for an application where lead times were never more than 20 working days. In such cases I don't think any other approach would make much difference.

This reply was modified 5 years ago by steve 14359.

sterling3721 SSChasing Mays Points: 653 More actions · Answer 5

thank you for your update. Logically speaking, after adding @increment to @loopDate, the new @loopDate could still be a holiday(for example, two consecutive holidays on Wed and Thu), so, the returned @loopDate could be a holiday in this case.

if exists(select ID from tblHoliday where HolidayDate=@LoopDate)

begin

set @LoopDate=DATEADD(DAY,@increment,@LoopDate)

end

Jeff Moden SSC Guru Points: 1003861 More actions · Answer 6

steve 14359 wrote:

Thom A, I could imagine your suggestion might result in better performance if large offsets are added (or subtracted) from a date. However the function was designed for an application where lead times were never more than 20 working days. In such cases I don't think any other approach would make much difference.

While I seriously appreciate anyone and everyone that will step up and publish a script, I have to agree with Thom on this one. A lot of people get into a heap of performance trouble when they design only for the current work load. That's not a slam... just "old dude" advice because I've seen the problem happen so very often.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

dbishop Mr or Mrs. 500 Points: 565 More actions · Answer 7

I have to agree with Jeff for yet another reason. While this particular function was written for this particular application, and you will NEVER be looking more than 20 days either way, the fact that the function is out there, the next person who needs to calculate working days is going to see a function named fns_AddWorkingDays() and say, "Hey, I can just use this one." Unfortunately, he needs to run it in a query that is going to calculate 200 days from an order date against 250K orders and performance is going to SUCK!

ScottPletcher SSC Guru Points: 100946 More actions · Answer 8

Here's a sample function using a physical tally table (it's not worth the trouble to me to try to use an inline tally table within a scalar function). I've put code to do the one-time create of the tally table in a separate code block.

SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
GO
ALTER FUNCTION [dbo].[fns_AddWorkingDays] 
(
@StartDate datetime,
@NumberOfWorkingDays smallint
)
RETURNS datetime
AS 
BEGIN
RETURN (    
    SELECT MAX(WorkDate) AS WorkDate
    FROM (
    SELECT TOP (@NumberOfWorkingDays) DATEADD(DAY, t.number, @StartDate) AS WorkDate
    FROM dbo.tally t
    WHERE
        t.number > 0 AND
        /*0=Monday; 0-4=Mon thru Fri; 5=Sat,6=Sun; regardless of @@DATEFIRST setting.*/
        DATEDIFF(DAY, 0, DATEADD(DAY, t.number, @StartDate)) % 7 <= 4 AND
        NOT EXISTS(SELECT 1 FROM tblHoliday tH WHERE tH.HolidayDate = 
            DATEADD(DAY, t.number, CAST(@StartDate AS date)))
    ORDER BY t.number
    ) AS derived
)
END

IF OBJECT_ID('dbo.tally') IS NOT NULL
    DROP TABLE dbo.tally;

CREATE TABLE dbo.tally (
    number int NOT NULL,
    CONSTRAINT tally__PK PRIMARY KEY CLUSTERED ( number ) WITH ( FILLFACTOR = 100 )
    );
INSERT INTO dbo.tally VALUES(0);

;WITH
cteTally10 AS (
    SELECT Number FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS Numbers(Number)
),
cteTally100 AS (
    SELECT 1 AS number
    FROM cteTally10 c1
    CROSS JOIN cteTally10 c2
),
cteTally10K AS (
    SELECT 1 AS number
    FROM cteTally100 c1
    CROSS JOIN cteTally100 c2
),
cteTally1Mil AS (
    SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS number
    FROM cteTally100 c1
    CROSS JOIN cteTally10K c2
)
INSERT INTO dbo.tally
SELECT number
FROM cteTally1Mil;

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

Jeff Moden SSC Guru Points: 1003861 More actions · Answer 9

p.s. Since the forum software still provides no easy method back to the article being discussed, here's the link to the original script article, if anyone is interested.

https://www.sqlservercentral.com/scripts/add-working-days-but-avoid-holidays-2

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

rchantler SSCrazy Points: 2128 More actions · Answer 10

We need to do this type of thing but for many countries, some of them whose work week is not Monday to Friday. Our solution is to maintain an international calendar table. That's a fair bit of work, but it does simplify calculations such as this one.

steve 14359 SSC Enthusiast Points: 182 More actions · Answer 11

I agree. My original coding contained a trap for those who did not realise that “holiday” is just a (very) special case of “non-working time”.

Anyone who has done this kind of thing for real will almost certainly have been asked for the general case - which specifies resource-specific non-working time (that is non-working time for each employee or physical or service resource). When such a situation becomes dynamic - when the calculation of a date results in that date becoming”non-working” for one or more resources then simplistic solutions involving tally tables become impractical and the maintenance of a table of non-working dates becomes imperative. In the original code the table of holidays becomes a table of non-working dates linked to each resource or coded to apply to “all”. The equivalent of “Saturday” and “Sunday” could also be coded into this table (or a linked table) and the coding could be altered to account for this.

habib SSC Enthusiast Points: 182 More actions · Answer 12

i just posted an article that uses a tally table. Substitute the holiday table with a resource-specific holiday/Non-working table, if needed.

Jeff Moden SSC Guru Points: 1003861 More actions · Answer 13

habib wrote:

i just posted an article that uses a tally table. Substitute the holiday table with a resource-specific holiday/Non-working table, if needed.

Congratulations but it doesn't help if we don't have a link to your article.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

habib SSC Enthusiast Points: 182 More actions · Answer 14

Sorry. I should withdraw that comment, at least temporarily.

The article (Script actually) has not been released for publication - i posted it just today.