Holidays and Calendar Generation

  • SqlNightOwl

    SSCrazy

    Points: 2214

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

    --Paul Hunter

  • Orlando Colamatteo

    SSC Guru

    Points: 182268

    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

    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

    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: 2214

    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: 2214

    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: 61036

    Interesting article. Well done sir.

    -- Alan Burstein


    Helpful links:Best practices for getting help on SQLServerCentral -- Jeff ModenHow to Post Performance Problems -- Gail ShawNasty fast set-based string manipulation functions:For splitting strings try DelimitedSplit8K or DelimitedSplit8K_LEAD (SQL Server 2012+)To split strings based on patterns try PatternSplitCMNeed to clean or transform a string? try NGrams, PatExclude8K, PatReplace8K, DigitsOnlyEE, or Translate8KI 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

    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

    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: 13685

    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, MCSE-DP, MVPToronto PASS Chapter[/url]

  • SqlNightOwl

    SSCrazy

    Points: 2214

    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: 2214

    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: 10957

    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: 13685

    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, MCSE-DP, MVPToronto PASS Chapter[/url]

  • SqlNightOwl

    SSCrazy

    Points: 2214

    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

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

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