Holidays and Calendar Generation

Question

Holidays and Calendar Generation

SqlNightOwl

SSCrazy

Points: 2332
More actions
February 15, 2016 at 9:51 pm

#304675

Comments posted to this topic are about the item Holidays and Calendar Generation
--Paul Hunter

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

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

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 1

I have seen holidays stored as bit-columns on traditional calendar tables that contain one row for each date and have seen them normalized out to separate tables. Thanks for sharing your solution.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Chris Quinn-821458 Hall of Fame Points: 3192 More actions · Answer 2

In the UK if a holiday such as Christmas Day, Boxing Day or New Year's day falls at the weekend, the public (bank) holiday always moves to the start of the next week - it never gets pushed into the previous week. So if, for example, Christmas day falls on a Saturday and boxing day (26th Dec) on the Sunday, the public holidays will be on Monday 27th and Tuesday 28th.

michael.sasso Valued Member Points: 54 More actions · Answer 3

One little quirk is the US Federal Reserve System. If a holiday falls on a Sunday then Federal Reserve banks and branches are closed the following Monday. However, if a holiday falls on a Saturday they are open on the preceding Friday. This may be important if you have a system that moves money electronically. The Friday before a holiday is a valid date for transactions.

SqlNightOwl SSCrazy Points: 2332 More actions · Answer 4

Chris Quinn-821458 (2/16/2016)
In the UK if a holiday such as Christmas Day, Boxing Day or New Year's day falls at the weekend, the public (bank) holiday always moves to the start of the next week - it never gets pushed into the previous week. So if, for example, Christmas day falls on a Saturday and boxing day (26th Dec) on the Sunday, the public holidays will be on Monday 27th and Tuesday 28th.

Thank you for that information Chris. As you may have guessed, this is a US-centric routine. If you would you provide a link that describes rules for the UK, I'll incorporate them into an update.

--Paul Hunter

SqlNightOwl SSCrazy Points: 2332 More actions · Answer 5

michael.sasso (2/16/2016)
One little quirk is the US Federal Reserve System. If a holiday falls on a Sunday then Federal Reserve banks and branches are closed the following Monday. However, if a holiday falls on a Saturday they are open on the preceding Friday. This may be important if you have a system that moves money electronically. The Friday before a holiday is a valid date for transactions.

Thank you Michael. Floating holidays are addressed with the IsFloating bit value in the table. I've had feedback that floating holiday ruled vary among the countries and I'll address that in an update.

--Paul Hunter

Alan Burstein SSC Guru Points: 61141 More actions · Answer 6

Interesting article. Well done sir.

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

michael.sasso Valued Member Points: 54 More actions · Answer 7

You float Saturday holidays to Friday and Sunday holidays to Monday. However, Federal Reserve holidays only float if the holiday is a Sunday, when they move to Monday. Saturday holidays effectively disappear.

This can be important if you have a system in which you want to schedule money movement for a future date and want to make sure the selected date is not a holiday. It's quite likely that the Friday before a Saturday holiday should not be invalidated.

Chris Quinn-821458 Hall of Fame Points: 3192 More actions · Answer 8

SQLNightOwl (2/16/2016)
Chris Quinn-821458 (2/16/2016)
In the UK if a holiday such as Christmas Day, Boxing Day or New Year's day falls at the weekend, the public (bank) holiday always moves to the start of the next week - it never gets pushed into the previous week. So if, for example, Christmas day falls on a Saturday and boxing day (26th Dec) on the Sunday, the public holidays will be on Monday 27th and Tuesday 28th.
Thank you for that information Chris. As you may have guessed, this is a US-centric routine. If you would you provide a link that describes rules for the UK, I'll incorporate them into an update.

https://www.gov.uk/bank-holidays

"If a bank holiday is on a weekend, a ‘substitute’ weekday becomes a bank holiday, normally the following Monday."

g.britton SSChampion Points: 13863 More actions · Answer 9

There's an easier way:

1. Set up a date table (aka DimDate) (examples abound: google is your friend)

2. Have a yearly process to update the table with new info (e.g. fiscal year, holidays etc.) takes about 30-60 minutes per year

3. Define FKs where you now have datetime columns, pointing to DimDate (or whatever you call it)

4. Join on the date table when you need date info.

The thing is, I've always found it necessary to have human eyes on the date table. Things change from year to year (even the start/end of a fiscal year and what days are statutory holidays).

Gerald Britton, Pluralsight courses

SqlNightOwl SSCrazy Points: 2332 More actions · Answer 10

I see, in that case feel free to change the behavior of how the function works for your environment. My focus was based on how federal holidays are observed for a trading application. That's why I included Good Friday as it's not a federal holiday, but is widely observed in the (US) trading business. Any special situation floating holidays such as you describe and and should be handled as one-off situations. I can see expanding the results to include a column for IsFederalReserveClosed. All that would need is to know if it's a "floating" holiday that "floated" forward.

--Paul Hunter

SqlNightOwl SSCrazy Points: 2332 More actions · Answer 11

g.britton (2/16/2016)
There's an easier way:
1. Set up a date table (aka DimDate) (examples abound: google is your friend)
2. Have a yearly process to update the table with new info (e.g. fiscal year, holidays etc.) takes about 30-60 minutes per year
3. Define FKs where you now have datetime columns, pointing to DimDate (or whatever you call it)
4. Join on the date table when you need date info.
The thing is, I've always found it necessary to have human eyes on the date table. Things change from year to year (even the start/end of a fiscal year and what days are statutory holidays).

This is but one way to solve the issue of holidays and calendars. I could see where this became part of a "self-service" procedure. A user requests information on business dates between now and 18 months from now. The procedure determines if that data is in the permanent table. If not, the range is calculated, saved to the permanent table and the results returned to the caller.

--Paul Hunter

Robert Sterbal SSChampion Points: 11050 More actions · Answer 12

I always find this webpage helpful: http://www.timeanddate.com/holidays/us/ for figuring out all the different details.

412-977-3526 call/text

g.britton SSChampion Points: 13863 More actions · Answer 13

SQLNightOwl (2/16/2016)
g.britton (2/16/2016)
There's an easier way:
1. Set up a date table (aka DimDate) (examples abound: google is your friend)
2. Have a yearly process to update the table with new info (e.g. fiscal year, holidays etc.) takes about 30-60 minutes per year
3. Define FKs where you now have datetime columns, pointing to DimDate (or whatever you call it)
4. Join on the date table when you need date info.
The thing is, I've always found it necessary to have human eyes on the date table. Things change from year to year (even the start/end of a fiscal year and what days are statutory holidays).
This is but one way to solve the issue of holidays and calendars. I could see where this became part of a "self-service" procedure. A user requests information on business dates between now and 18 months from now. The procedure determines if that data is in the permanent table. If not, the range is calculated, saved to the permanent table and the results returned to the caller.

I'd advise against calculating this data. Much better to find the data steward and get the table updated. What are the chances that at some point in the future the rules will change but the procedure left as-is (and wrong)? 100%, in the limit

Gerald Britton, Pluralsight courses

SqlNightOwl SSCrazy Points: 2332 More actions · Answer 14

Not all companies have the benefit of having a Data Steward. I've done this for a number of years for both large and small companies. My experience is that the Data Steward position is filled part-time by someone that's fortunate to spend 4 hours a year in the role. 😀

There are benefits to dynamically creating the data (i.e. no storage) just as there are benefits to storing the data (no calculation). If you walk into a position and there's nothing in place, then this article provides a starting point. The consultants favorite phrase definitely applies here... it depends on what you situation is. Your implementation of this code will vary according to the needs of your application. I'd definitely pre-calculate and store the data in a Data Warehouse environment. If all you'd doing is putting up a list of holidays your company observes then - maybe. Is the company huge with millions of hits for that info, is this info used in as part of an audited system, or is this a more modest enterprise?

--Paul Hunter