September 18, 2016 at 1:22 am
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
September 19, 2016 at 7:12 am
Thanks for the humor in it too.
September 19, 2016 at 9:54 am
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-2 int = @yr /100;
declare @C int = @yr % 100;
declare @D int = @b-2/4;
declare @E int = @b-2 % 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 @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
September 19, 2016 at 2:35 pm
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
September 19, 2016 at 4:23 pm
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
September 19, 2016 at 4:32 pm
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
September 19, 2016 at 4:48 pm
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
September 19, 2016 at 5:22 pm
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.
September 20, 2016 at 3:21 am
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!
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
September 20, 2016 at 8:40 am
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
September 20, 2016 at 9:09 am
Hey, Check out the article I wrote for technet about the same thing.
It comprises some functions to help calculate holidays.
September 20, 2016 at 1:10 pm
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
September 20, 2016 at 1:45 pm
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.
September 20, 2016 at 2:45 pm
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
September 20, 2016 at 2:56 pm
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 20 total)
You must be logged in to reply to this topic. Login to reply