Calendar / Date Tables – An Introduction
What is a Calendar or Date table?
Maybe you’ve worked with data warehouses before, in which case the concept of a “Date Dimension” is going to be familiar. If not, the general idea behind a Calendar or Date table is that you have a table of Dates and metadata about those dates. This can include business-specific flags, alternate Quarter structures, alternate Week Start data, or whatever fits your needs
Why would you want to use one?
Many times you need the ability to store a date, but then have to calculate other details for reporting. What Fiscal quarter is this? Was this a day the business was open? What day of the month is this? What day of the Quarter is this? You can use the built-in functions to figure all of that out, but sometimes it’s easier to just tie over to a Calendar table and then aggregate based on something already determined. Sometimes you need to have an alternate Week structure compared to other parts of the business so your business runs W-Tu. That can be determined in the Calendar table so you can aggregate based on a custom week number without needing to calculate that on the fly.
If you don’t do a lot of date-heavy calculations, there may not be as many advantages to this, but I’ve almost always found these helpful once you start needing details that are related to date calculations. Those sorts of situations come up more often than I’ve realized from “how many days of work were missed this quarter” to “give me the first day of the month”. (SQL gives us an End of Month function, but not a first of month function at the time of writing.)
How do you get started?
One of the better articles I’ve found was from ChilledSQL.com. There’s a good overview of a calendar table that covers a lot of the more common date data. I’ve tweaked this a little to contain a CREATE TABLE and a Stored Proc that will populate the table. Generally, this is a one-time population, though sometimes you may want to populate a smaller date range and extend as needed.
That said, start by looking over the examples above and in the gists below. That should give you some ideas of what you’ll be storing and how to adjust the table to meet your needs. Maybe you need an extra flag to indicate that these dates are in “busy season”. Maybe you need to know when there’s a regular company shutdown for manufacturing. Add those types of columns and populate them. Perhaps you always use certain formats for dates when you write reports. Those are easy enough to add as a varchar column and populate with a given format. I’ve usually populated them out for up to 10-20 years past the current year to have enough room to be useful, but not so much that people inadvertently pull in a couple of decades of future dates.
Start with creating a Calendar table:
Then populate the Calendar table. I prefer to use a stored proc if possible.
After calling the stored procedure w/ some start/end date values, you should have something to query. You can join any existing queries to your Calendar table by the DATE values and add columns from the Calendar table to your SELECT or WHERE clauses.
Examples:
I chose to use the WideWorldImporters sample database, though the concept could easily be used against any database with DATE or DATETIME types. You might have to do a CAST(datetime_column as DATE) to do the comparison so watch for overhead with that sort of query. But here is an example of easily filtering down to the year and date without extra functions to parse that data. While those aren’t too hard to write, I can filter further into whether the invoice was on a weekday or weekend, or which day of the week was used. This can be used for trend analysis pretty quickly in tools such as PowerBI or even Excel by pulling in the various date metadata columns for aggregation. Hopefully this will give you some ideas to get started with a Calendar table. Adjust accordingly for your needs or naming standards and hopefully this will reduce some of the more difficult to parse date calculations you’re doing in your code.
USE WideWorldImporters
GO
--Test query to get some invoice data
select c.Calendar_Year, c.Calendar_Month
, SUM(il.ExtendedPrice) as TotalInvoiceAmount
, SUM(il.TaxAmount) as TotalTaxAmount
, SUM(il.LineProfit) as TotalLineProfit
from Sales.Invoices as i
JOIN Sales.InvoiceLines as il
ON i.InvoiceID = il.InvoiceID
JOIN Calendar as c
ON i.InvoiceDate = c.Calendar_Date
GROUP BY c.Calendar_Year, c.Calendar_Month
ORDER BY c.Calendar_Year, c.Calendar_Month
--Similar Query, but broken down into whether the invoice was on a weekday or not
select c.Calendar_Year, c.Calendar_Month, c.Is_WeekDay
, SUM(il.ExtendedPrice) as TotalInvoiceAmount
, SUM(il.TaxAmount) as TotalTaxAmount
, SUM(il.LineProfit) as TotalLineProfit
from Sales.Invoices as i
JOIN Sales.InvoiceLines as il
ON i.InvoiceID = il.InvoiceID
JOIN Calendar as c
ON i.InvoiceDate = c.Calendar_Date
GROUP BY c.Calendar_Year, c.Calendar_Month, c.Is_WeekDay
ORDER BY c.Calendar_Year, c.Calendar_Month
Good post! I love calendar tables, but I usually go the extra mile, for example, columns for a holiday or business days. Then I create columns that I populate with offsets – normal daily offset (rownum), business day offset so it’s easy to index for queries like today + 10 business days.
The biggest challenge was the Easter holiday as I had to look up the definition and calculation. There is also a challenge with week numbers and iso_week as some countries start the week on Sunday, some on Monday.