Building a Calendar Table in SQL
Explanations and ruminations below, full code is here.
A calendar table or data structure in a SQL database can be essential for completing several date logic tasks. This can include generating records between two dates, counting the business days between two dates, or identifying other important dates such as ETL task days or holidays. This post walks through how to code out a custom calendar data structure in your database.
Before jumping into the code, please note that you may be able to download and import a calendar data set that fits your needs. Or, your database may already have a calendar table available. If these options aren’t available to you or don’t fit your needs, I hope the approach below helps. It’s rather malleable and can be spun up rather quickly.
I enjoyed figuring this code out as it was a good test of problem-solving and included using some of my favorite SQL functions (namely a WHILE loop and window functions with OVER clauses). Most simply, our goals when creating a calendar table are to (1) generate the dates we want, (2) add our desired labels, and then (3) insert that data into a usable data structure. For this example, I want daily dates over several years. I want the following labels: day of week (DOW), if the day is a business day, and if the day is a holiday. Last, I will store the calendar in a temp table.
As noted, this solution is custom. You can generate a calendar of months rather than days. You can add different labels, maybe you care about the second Thursday of every month. And you can store the data in a regular table, CTE, or table variable if you want. Ultimately, you should be able to get to an output of your liking. Here’s mine:
Ok, our first steps are to create the data structure where we want to store our calendar and set the range for the dates we’ll be entering into the table. Below, we’re creating our temp table, adding the fields we want, and setting the date range for our calendar from ‘2019–01–01’ until 5 years after this year’s December:
Now, the fun part: inserting our dates. We will be using a WHILE loop to do this. WHILE loops in SQL are relatively new and provide both highly useful functionality and an opportunity to dabble, if ever so slightly, in object-oriented concepts. For those of us who may be coming from predominantly SQL backgrounds, it’s a gentle and helpful introduction to these concepts.
The WHILE loop will perform our desired tasks until its termination condition is met. Here, we’re asking it to start at the date we passed to the @startDate variable. Then, insert the data points we create into our temp table. Last, we add a day to our @startDate variable. The loop repeats this process until our termination condition is met. In this case, it terminates if our @startDate variable is no longer less than or equal to our @endDate variable. Note, we are able to populate most of our additional fields in this initial loop:
From what we’ve done so far, we have a usable calendar reference data set now available in our database:
If your requirements only ask for the date, day of the week, and identifying Saturdays and Sundays as non-business days, you’re good to go! In our use case, we will go a bit further. And that’s the benefit of being able to spin up a custom calendar data set, you can adjust it to fit your particular use case. Here, we will look to add some recognized holidays and designate them as non-business days. As you can see in the highlighted row of the picture above, the Fourth of July in 2022 falls on a Monday and is currently considered a business day in our table.
From a calendar table perspective (if it exists), holidays fall into two groups: (1) those that fall on the same date every year (static), and (2) those that are determined by date rules (variable) (e.g. Thanksgiving is the 4th Thursday of every November). Accordingly, though the approach to handling each group will be similar, there will be differences in logic.
First, we can address static holidays. The way forward seems simple, identify holidays then update our temp table. Cool:
So, we throw some holidays that fall on the same day every year into a table variable. To include additional holidays or dates that fall on the same date each year, you can simply add them to the table variable. Next, we identify the full date we’ll want to update on our calendar table in a CTE by identifying all the relevant month and day combos across the years included in our calendar table. Last, we update the appropriate fields on our calendar temp table: unmark each as a business day and mark each as a holiday. Looking back to our Fourth of July example from above, we get the desired result:
Great! Now the Fourth is no longer recognized as a business day in our table. So, if I’m adding up the business days in a date range that includes the Fourth, I’ll no longer count that holiday. Depending on your use case, you might be set for static holidays. Whether you need additional handling depends on how you feel about Christmas 2021:
Hopefully, your IRL Christmas 2021 was awesome! The Christmas question we have to consider here is caused by the holiday falling on a Saturday. Should we consider the day before (Friday) a holiday? A lot of organizations would, and we will here. We’ll look to enact a simple rule a good number of organizations adhere to when holidays fall on a weekend: if the holiday falls on a Saturday, the preceding Friday is considered a non-business day. If the holiday falls on a Sunday, the following Monday is considered a non-business day.
To handle holidays that fall on the weekend, we will expand the logic of our EXISTS conditions. Currently, we identify static holidays as days in our calendar table that have the same month and day as the value pairings in our table variable. We now also want to include Mondays after Sunday holidays and Fridays before Saturday holidays. We can do this by adding two additional EXISTS conditions. One checks to see if a date in our calendar table is the same as our designated static holiday + 1 day AND that date is a Monday. The other catches Saturday holidays by checking our calendar table for dates that are the same as our designated holiday - 1 day AND that date is a Friday. Note, we will have to add a bit of custom handling in case New Year's falls on a Saturday
Our previous update statement now becomes:
Let’s check in on our calendar table Christmas 2021:
Nice! We’re now identifying additional days we wouldn’t consider business days when our static holidays fall on a weekend. Now we can move on to the last piece of our puzzle, handling variable holidays.
Variable holidays like Thanksgiving or Memorial Day all follow the same type of 3-part rule, they: (1) fall in the same month every year, (2) on a certain day in that month, and (3) on the same occurrence of that day in that month (e.g. the last Monday of a month, or the 4th Thursday). Our variable holiday solution will follow the same pattern as the one for our static holidays: store the holidays we want to identify in a variable, then identify all of the relevant dates from our calendar table in a CTE so we can then update our calendar table.
For variable holidays, we need to pass the (1) month the holiday falls in, (2) the day number (e.g. 2 = Monday), and (3) the relevant occurrence of that day in that month. Note, we’ll use 99 to denote when the relevant occurrence is the last occurrence of that day in a given month (e.g. Memorial Day is the last Monday of May). Then, we will create a look from our existing calendar table that will allow us to identify all the variable holiday dates in the table. Last, we will update those dates in our calendar table:
Way back at the beginning of this post, I mentioned an affinity for window functions with OVER clauses in SQL. In our prep CTE we get to use one that most SQL users are used to, ROW_NUMBER(). The critical piece of this function for our calendar table is the ability to define the window of the function’s interaction with the data set. To do this, we include the PARTITION BY argument in our OVER clause to identify the data window within which we want the row numbering to take place.
Accordingly, we set the window to count each occurrence of each day in each month of a year. To identify the last occurrence, we add another field to our CTE and flip the ORDER BY clause condition to DESC. We can then add 98 to that field’s ROW_NUMBER() value to align with the logic we implemented for identifying the last occurrence of a day in a month. Below is how the prep CTE frames November of 2023. Based on our rules, we will be able to identify 11–23 as Thanksgiving in 2023 and then update our calendar table:
So, we’re done! This is the full code. Remember, it’s malleable. Trust, you can make it better.
Random final note(s):
Writing this piece of code came about somewhat randomly for me. It was actually in prep for writing a how-to on a pretty simple custom date function I wrote. I’m grateful I got to go down this side path. It was a fun challenge that tested several SQL coding concepts. Now, it’s a task I would give intermediate SQL developers in the future for skill development.
