none
Holiday Calendar

    Question

  • Hello and happy new year!

    I wrote a stored proc that basically aggregates some data based on how many Monday's, Tuesday's, Wednesday's etc in a current month (date range parameters). The proc works great, however I need to filter out holidays to get the correct number of days for the divisor.

    My question is... What is the best way to approach this? I was considering creating a holiday table and manually entering holidays up until 2020, but that seems like a rookie move.

    What are some dynamic approaches to this?
    Any help is greatly appreciated.

    Dave


    Dave SQL Developer

    Tuesday, January 07, 2014 7:31 PM

Answers

  • Why is it a rookie move?  On what principle is SQL Server supposed to infer that a date is a holiday?  My guess is that every day is some kind of holiday somewhere, but is it a holiday for you?  Is Columbus day a holiday?  If you are a bank, yes, but I'm guessing you don't even know or care what Columbus day is.  Same with Martin Luther King's birthday.

    I can't think of any way to identify holidays that are relevant without a table.


    Russel Loski, MCT, MCSE Data Platform/Business Intelligence. Twitter: @sqlmovers; blog: www.sqlmovers.com

    • Marked as answer by DaveDB Tuesday, January 07, 2014 7:40 PM
    Tuesday, January 07, 2014 7:39 PM

All replies

  • Why is it a rookie move?  On what principle is SQL Server supposed to infer that a date is a holiday?  My guess is that every day is some kind of holiday somewhere, but is it a holiday for you?  Is Columbus day a holiday?  If you are a bank, yes, but I'm guessing you don't even know or care what Columbus day is.  Same with Martin Luther King's birthday.

    I can't think of any way to identify holidays that are relevant without a table.


    Russel Loski, MCT, MCSE Data Platform/Business Intelligence. Twitter: @sqlmovers; blog: www.sqlmovers.com

    • Marked as answer by DaveDB Tuesday, January 07, 2014 7:40 PM
    Tuesday, January 07, 2014 7:39 PM
  • you need to create a table period where it stores all the dates(days,Workingday,Non-workingday,months,week,year). update the columns with flag  where there is a holidays. 

    --Prashanth

    Tuesday, January 07, 2014 7:40 PM
  • Makes sense. I just wanted to make sure there wasn't a more efficient way of doing this. It's good to know that my initial instinct was correct.

    Thanks for your help,

    Dave


    Dave SQL Developer

    Tuesday, January 07, 2014 7:42 PM
  • Sure, you need a Calendar table. Normally, it is not just Holidays table, it's a Calendar table similar to the described in this blog

    http://social.technet.microsoft.com/wiki/contents/articles/19670.t-sql-useful-links.aspx#Calendar_table (link to the blog should be at that link)


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Tuesday, January 07, 2014 7:46 PM
  • Derek Dongray came up with a classification of the public holidays and weekends he needed to work with in multiple countries. Here is his list with more added.
    1. Fixed date every year.
    2. Days relative to Easter.
    3. Fixed date but will slide to next Monday if on a weekend
    4. Fixed date but slides to Monday if Saturday or Tuesday if Sunday (UK Boxing Day is the only one).
    5. Specific day of week after a given date (usually first/last Monday in a month but can be other days, e.g. First Thursday after November 22 = Thanksgiving)
    6. Days relative to Greek Orthodox Easter (not always the same as Western Easter)
    7. Fixed date in Hijri (Muslim) Calendar - this turns out to only be approximate due to the way the calendar works. An Imam has to see a full moon to begin the cycle and declare it. 
    8. Days relative to previous Winter Solstice (Chinese holiday of Qing Ming Ji. Civil holidays set by decree, such as a National Day Of Mourning. 
    10. Fixed date except Saturday slides to Friday, and Sunday slides to Monday. 
    11. Fixed date, but Tuesday slides to Monday, and Thursday to Friday. (Argentina celebrates October 12, the day Columbus discovered America is a national holiday in Argentina. Except when it's a Tuesday, they back it one day to Monday.

    As you can see, some of these are getting a bit esoteric and a bit fuzzy. A calendar table for US Secular holidays can be built from the data at this website, so you will get the three-day weekends: 

    http://www.smart.net/~mmontes/ushols.html

    What did you do for Thanks-Hanukkah last year?  A turkey stuffed with chopped liver? :)


    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Tuesday, January 07, 2014 10:32 PM
  • We light the menorah but we eat kosher turkey as well :)

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Tuesday, January 07, 2014 10:43 PM