In any data warehousing or analytics solution, you will eventually need to generate a calendar table in order to perform time intelligence analytics.

One key aspect that most businesses will require is to view KPIs by working days only i.e. days when the business was operating normally. Each business will have their own unique rule sets as to what constitutes a work day, but there are a few standard rules that should apply to the vast majority (perhaps excluding retail).

Namely: Any day is a working day except

• Weekends – Saturday & Sunday

• Public Holidays

Weekends are pretty easy to identify, but public holidays not as much. Most public holidays are associated with fixed dates e.g. Christmas is always December 25th. But the most elusive one is Easter Sunday, because it does not occur on a fixed date each year.

In this post, I will demonstrate a technique to dynamically calculate the date of Easter Sunday so that you can allocate that public holiday dynamically on your calendar table in an SQL relational database system without the need for a manually curated list of public holidays.

Quick Maths 

According to Wikipedia : 

Easter is celebrated on the first Sunday after the Paschal full moon, which is the first full moon on or after 21 March (a fixed approximation of the March equinox). Determining this date in advance requires a correlation between the lunar months and the solar year, while also accounting for the month, date, and weekday of the Julian or Gregorian calendar.[4] The complexity of the algorithm arises because of the desire to associate the date of Easter with the date of the Jewish feast of Passover which, Christians believe, is when Jesus was crucified. 

There are a few algorithms to calculate Easter Sunday taking the above into account. One such algorithm is the Meeus’s Julian algorithm. 

Fellow SQL Nerd sqlsunday.com posted this article called “Calculating The Date Of Easter Sunday” on 2014-07-05. 

He essentially took the Meeus’s Julian algorithm and SQL’ified it. 

I then decided to alter his code to my own flavor of syntax to produce the below Scalar User Defined Function. 

Scalar User Defined Function Version

Table Valued User Defined Function Version

On the next episode of Dragon ball Z

These functions are nifty, but need to be incorporated into larger Calendar generation procedures.

Look out for future posts that show just how to do that. 

Thank you for reading my ramblings, if you want to you can buy me a coffee here:

Support Dian Germishuizen on Ko-fi! ❤️