Function To Add [n] Days to A Date and Adjust to the Nearest Business Date

Question

Function To Add [n] Days to A Date and Adjust to the Nearest Business Date

Viewing 4 posts - 16 through 18 (of 18 total)

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

andrew gothard SSChampion Points: 12301 More actions · Answer 1

david.holley (4/24/2013)
Woah! My brain just exploded (a la Mars Attacks!). That'll take some time to digest. It didn't occur to me until just now that most major US holidays observed by private non-banking businesses fall on a specific date, with the exception of Thanksgiving which can be defined as the Thursday where the remaining days in the month is less than 6.
(And just why hasn't MS modified DateDiff and DateAdd across all languages to allow for caluclations such as number of business days between two dates? Since it does come up quite frequently.)

And then there's Easter. Plus, I don't know about the US, but in the UK, id xmas falls on a weekend then the public holiday moves to the next Monday ...

So at the end of the day, you have the option of an increasingly complex and difficult to maintain sp. or a straightforward Date table where the code for generating one (including these issues) is easy to find, and if a special day's holiday is announced for some reason, it's a simple case of updating the table. It makes the calculation very straightforward

I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.

david.holley Hall of Fame Points: 3627 More actions · Answer 2

I was thinking holidays where a business is closed and then just US holidays. Of course, it'd also be nice if MS were to do something whereby they published worldwide holidays of interest and allowed that information to be accessed via code.

Perhaps adding .IsHoliday() and .IsHoliday(country) (boolean) and Holidays (collection)

The thinking behind .IsHoliday(country) is that you could take an address such as a company, employee home, client office address and determine if that location will be observing a holiday on a particular date such as Contract Due, Price Deadline, etc.

The Holidays collection would allow you to loop through the Holidays occurring on that date retriving specifics such as nationally observed, governmental, unobserved (Halloween). It would also allow you to retrieve Holidays at a national or worldwide level as needed.

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 3

Chris,

In reference to the following line of code in your fine function...

E3(N) AS (SELECT 1 FROM E2 a CROSS JOIN E2 b CROSS JOIN E1 c), --1M rows max

I believe you'll find that only goes up to 100k rows because of the E1 reference. You'd need to change that to E2 to get the full million rows.

--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)
Intro to Tally Tables and Functions

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

Jeff Moden (4/24/2013)
Chris,
In reference to the following line of code in your fine function...
E3(N) AS (SELECT 1 FROM E2 a CROSS JOIN E2 b CROSS JOIN E1 c), --1M rows max
I believe you'll find that only goes up to 100k rows because of the E1 reference. You'd need to change that to E2 to get the full million rows.

You're right, and the 100000 row choke is deliberate. The comment remains from the very well known paper I copied the code from 😉 Thanks Jeff.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden