The Bones of SQL - The Holiday Table

  • The Dixie Flatline

    SSC Guru

    Points: 53253

    Comments posted to this topic are about the item The Bones of SQL - The Holiday Table

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    Thanks for the humor in it too.

  • csnyder4

    SSC Enthusiast

    Points: 167

    FWIW,

    Here is a general method of calculating the date Easter (always on a Sunday) and Good Friday (always on the preceding Friday). Many companies will treat Good Friday as a non-work day.

    The date of Easter is defined as the first Sunday following the first full moon after the spring equinox (generally around March 19-21). So, it will vary based on the lunar cycle. I would expect that other religious holidays based on the lunar cycle will have a similar solution.

    from a Scientific American column by Ian Stewart in March, 2001

    http://www.whydomath.org/Reading_Room_Material/ian_stewart/2000_03.html

    As usual, the mathematicians are far ahead of the programmers. Gauss did a nearly perfect method back in the early 1800's.

    Knuth did a version in 1962,

    https://www.cs.drexel.edu/~mcs172/Sp06/lectures/10.2_enum/files/EasterDate.html

    and the NOAA method ... http://aa.usno.navy.mil/faq/docs/easter.php

    From the article ...

    Choose any year of the Gregorian calendar and call it x. To determine the date of Easter, carry out the following 10 calculations (it’s easy to program them on a computer):

    1. Divide x by 19 to get a quotient (which we ignore) and a remainder A. This is the year’s position in the 19-year lunar cycle. (A + 1 is the year’s Golden Number.)

    2. Divide x by 100 to get a quotient B and a remainder C.

    3. Divide B by 4 to get a quotient D and a remainder E.

    4. Divide 8B + 13 by 25 to get a quotient G and a remainder (which we ignore).

    5. Divide 19A + B – D – G + 15 by 30 to get a quotient (which we ignore) and a remainder H.

    (The year’s Epact is 23 – H when H is less than 24 and 53 – H otherwise.)

    6. Divide A + 11H by 319 to get a quotient M and a remainder (which we ignore).

    7. Divide C by 4 to get a quotient J and a remainder K.

    8. Divide 2E + 2J – K – H + M + 32 by 7 to get a quotient (which we ignore) and a remainder L.

    9. Divide H – M + L + 90 by 25 to get a quotient N and a remainder (which we ignore).

    10. Divide H – M + L + N + 19 by 32 to get a quotient (which we ignore) and a remainder P.Easter Sunday is the Pth day of the Nth month (N can be either 3 for March or 4 for April). The year’s dominical letter can be found by dividing 2E + 2J – K by 7 and taking the remainder (a remainder of 0 is equivalent to the letter A, 1 is equivalent to B, and so on).

    Let’s try this method for x = 2001: (1) A = 6; (2) B = 20, C = 1; (3) D = 5, E = 0; (4) G = 6; (5) H = 18; (6) M = 0; (7) J = 0, K = 1; (8) L = 6; (9) N = 4; (10) P = 15. So Easter 2001 is April 15.

    For the Western (Julian Calendar)

    CREATE function dbo.ccoc_fnEasterDate

    (

    @yr int

    )

    returns datetime

    as

    begin

    -- Declare the return variable here

    DECLARE @easterDate datetime;

    declare @a int = @yr %19;

    declare @B int = @yr /100;

    declare @C int = @yr % 100;

    declare @D int = @B/4;

    declare @E int = @B % 4;

    declare @G int = (@B * 8) / 25;

    declare @h int = ((19 * @a) + @B-@D-@G+15) % 30;

    declare @M int = (@A + (11 * @h))/319;

    declare @j-2 int = @C / 4;

    declare @k int = @C % 4;

    declare @L int = ((2 * @E) + (2 * @j-2)-@K-@H+@M+32) %7;

    declare @N int = (@H-@M+@L+90)/25;

    declare @P int = (@H-@M+@L+@N+19)%32;

    set @easterDate = dateadd(mm, (@yr - 1900) * 12 + @n - 1 , @p - 1)

    -- Return the result of the function

    return @easterDate;

    end

  • The Dixie Flatline

    SSC Guru

    Points: 53253

    Thanks for sharing that, Mr. Snyder !!

    I'll have to test it out against the Wiki list of Easter Sundays posted HERE.

    Maybe Steve will let me edit the article to include that code for future readers.

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

  • The Dixie Flatline

    SSC Guru

    Points: 53253

    Mr. Snyder, it works like a charm.

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

  • GPO

    SSCarpal Tunnel

    Points: 4556

    This and the calendar table articles are great pieces of work for people who aren't yet using their databases to their fullest potential. Forgive me if I'm missing something obvious, but my preferred option is to simply store public holidays in the calendar table. Where I live, a day's status as a public holiday waxes and wanes based on the vicissitudes of the government of the day. Elect a right-wing government, expect a narrow interpretation of what can be called a public holiday. Elect a left-wing government, expect a more liberal approach. This lends an exciting element of unpredictability to managing dates. One thing is certain. A day's status as a public holiday can't be changed retrospectively. The solution for us is very simple. We don't bother to "calculate" public holidays at all (whether as a one-off or as an ongoing exercise). We simply have columns in our calendar table that flag and describe public holidays. As they become known, they are added in (or taken away:crying:) - well actually the columns in question are updated. I would contend that you don't need a holiday table at all. It can be subsumed completely by the calendar table.

    ...One of the symptoms of an approaching nervous breakdown is the belief that ones work is terribly important.... Bertrand Russell

  • The Dixie Flatline

    SSC Guru

    Points: 53253

    GPO, you're absolutely right. There is no need for a holiday table when you are querying the calendar itself. In the situation you describe, flagging (and unflagging) the holiday bit in the calendar table is probably your best option, although a Holiday table might be good history to have if you are looking back to see why the third Wednesday in June was a holiday back in 2012.

    In the U.S., holidays don't bloom and wither so quickly, so a Holidays table comes in handy if you want to build out the calendar years in advance. I'm a provincial American, what else can I say? 🙂

    But the problem of building the Holiday table also makes a good showcase for some of my favorite set based techniques, as well as a few of the odder columns previously defined for the calendar table.

    Thanks for your comments.

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

  • csnyder4

    SSC Enthusiast

    Points: 167

    After all the stuff I've learned from this site. It's about time I kicked something back in. 😀

    http://www.oremus.org/liturgy/etc/ktf/app/easter.html

    is another explanation of how the calculation works. The one I posted seems to have some tweaks from this version.

  • Neil Burton

    SSC-Insane

    Points: 22205

    Christmas Day and New Year's Day always fall on the same date but the holiday may not actually fall on that day. In the UK, Christmas Day and Boxing Day (the 26th of December) are Bank Holidays. If Christmas Day falls on a weekend then the Bank Holiday is carried over to the next Monday and the corresponding Boxing Day Bank Holiday is moved to the Tuesday. This can mean a four day Christmas break. The same happens with the 1st and, in Scotland the 2nd, of January.

    I was going to post the code I used to handle these variations but I've just opened it up and it's shocking :blush: I'll have to improve on it before it can be seen in public!


    On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
    —Charles Babbage, Passages from the Life of a Philosopher

    How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537

  • The Dixie Flatline

    SSC Guru

    Points: 53253

    BWFC: I saw a comment this week that said "If you never look back over your old code and see things you could do better, it means you're never learning anything new." I hope you'll post that code here. I'm going to rewrite Mr. Snyder's Easter function as an inline table valued function, just cause I can.

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

  • phurst

    SSC Veteran

    Points: 235

    Hey, Check out the article I wrote for technet about the same thing.

    It comprises some functions to help calculate holidays.

    http://social.technet.microsoft.com/wiki/contents/articles/29260.sql-developers-toolbox-calendar-tables-explained.aspx

  • The Dixie Flatline

    SSC Guru

    Points: 53253

    Patrick: Thank you for advertising your own article here. I did in fact read it and it does contain quite a few columns missing from my article (Beginning of Previous Month, Previous Quarter End, etc.) which I'm sure are very useful for financial reporting. Readers of my first article may want to give it a look-see to give them additional ideas for their own calendar tables.

    However, the code in your article does several things I would never recommend to readers of my article, such as populating a table with a WHILE loop, using scalar functions, or creating a table and then updating it multiple times. I know the amount of data for a calendar or holiday table is trivial, so lesser performance creating it may be acceptable. But, I still urge newcomers to SQL to use set-based techniques whenever possible.

    Consider two very simple examples creating a table of years from 2000 to 2999. Functionally they do the same thing and for only 100 rows, timing is insignificant. But which is easier?.

    create table #tempyears1 (Year1 int primary key)

    insert into #tempyears1

    select top 100 1999+N as Year1

    from vtally

    create table #tempyears2 (Year2 int primary key)

    Declare @year int = 1999

    While @year < 2100

    begin

    set @year = @year + 1

    insert into #tempyears2

    select @year

    end

    Writing the WHILE loop required variable declaration, the logic to increment year, and the logic to exit the loop. The loop forced the system to do 100 individual inserts instead of a single insert of 100 rows. Done on a large scale this is known as "Death by a Thousand Cuts." There are certain situations where a WHILE loop may be necessary, but it should be a last resort since set-based code is not only better performing*, but in instances like this easier.

    (*Run for 1000 rows, the set-based technique takes an average of 13 ms compared to just over 600 ms for the while loop.)

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

  • phurst

    SSC Veteran

    Points: 235

    Hey Dixie,

    Sorry you misunderstood, the point was actually the holiday aspect, rather than the calendar table itself. The article itself is years old at this point, I've not much interest in promoting it for the sake of promotion (it's posted to technet, the only "reward" I'd get is a few more points at best), rather the sharing of the concepts. It made more sense to link to it, than post walls of code here instead.

    The functions presented for nthDayOfMonth, for example, and very helpful when figuring out things like Thanksgiving, Labor day, and the like.

    You'll also find a working version of the code previously mentioned in another reply that calculates Easter Sunday/Monday and Good Friday.

    While I appreciate the performance implications of loops over set based operations, there's little to no performance gain to be had, as it's likely the code to generate the data will only be run once a year, at most, with the resulting table being referenced instead.

    As a side note, the ends of the various ranges are indeed included, in columns such as PrevMonthEndTS, PrevQuarterEndTs and the like.

  • The Dixie Flatline

    SSC Guru

    Points: 53253

    Patrick,

    Sorry if I misunderstood, but your initial post didn't contribute anything to the discussion here. It simply said "Look at mine too." with no mention of what would be gained from looking. This isn't about pride of authorship, there are a number of Calendar table articles already out there, and have been for years. So I read yours and saw some things that new readers might want to consider. However I also feel obliged to warn them off things which are not best practices. WHILE loops are based procedural thinking, and that's something newcomers need to unlearn when working with SQL.

    The functions presented for nthDayOfMonth, for example, and very helpful when figuring out things like Thanksgiving, Labor day, and the like.

    A similar column is in my previous article. It's called DoWAsc. It was used to develop Thanksgiving, Labor Day, and Memorial day in the Holiday table article. I presume you stopped reading before you got to that part. I must confess your column name is much better, but again it's produced by a scalar function with a while loop. The nthDayOfMonth function is 20 lines long. How much time was spent coding then debugging it?

    The same result is produced in a set based manner using only:

    ROW_NUMBER() OVER(PARTITION BY YearNo,MonthNo,[DayofWeek] ORDER BY N

    I'm sorry but I do NOT want to encourage any newcomers to write scalar functions like nthDayOfMonth, when the set-based alternative is so much simpler.

    You'll also find a working version of the code previously mentioned in another reply that calculates Easter Sunday/Monday and Good Friday.

    Thanks but someone else already contributed that.

    While I appreciate the performance implications of loops over set based operations, there's little to no performance gain to be had, as it's likely the code to generate the data will only be run once a year, at most, with the resulting table being referenced instead.

    I believe I already conceded that performance was irrelevant for data amounts this small. I just don't like teaching newcomers bad habits.

    As a side note, the ends of the various ranges are indeed included, in columns such as PrevMonthEndTS, PrevQuarterEndTs and the like.

    Again, those columns were mentioned, and that's why I said readers might want to look, to get some ideas.

    Thanks, again.

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

  • phurst

    SSC Veteran

    Points: 235

    Dixie,

    I've no interest in competing, at all, which is all you seem interested in. That was not the spirit of my initial post. Have a good one.

    One last note, the initial reason for the while loops was the lack of availability of ROW_NUMBER(), and the other windowed functions on earlier versions of SQL Server.

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

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