1. Working with calendar tables
Next, we will look at one of the most powerful techniques available for working with dates in SQL Server: the calendar table.
2. What is a calendar table?
A calendar table stores date information for quick retrieval. Here are some examples in a calendar table.
We have an integer key, representing the date, along with the date itself, and other supporting columns.
3. Contents of a calendar table
A calendar table can hold lots of interesting data and can be broken into categories. First, we have general columns such as date, day name, and whether the day is a weekend.
Second, we have calendar year information. Most business processes work off the calendar year, so we care about things like being in the third calendar quarter or calendar year 2019.
Not all business processes work off a calendar year, however. Some work off a fiscal year. Calendar tables let us store information for both fiscal and calendar years so we can compare where they differ.
Finally, there are specialized columns which can be relevant to a particular business but perhaps not everywhere. For e-commerce sites, some holidays are critical, so we want to store those. We might also store information about lunar calendars or other entries.
A calendar table is built once and ideally never updated, so it should be treated like a warehouse dimension--meaning it contains a reference structure of descriptive attributes--and can be made as wide as we want.
4. Building a calendar table
Building a calendar table is quick: we define the columns we need. Here are a few sample columns.
For each day we want to include, we can use built-in functions to get most of the way there.
It's usually unnecessary to reinvent the wheel when making calendar tables. Date dimensions and calendar tables can be found online online and adapted.
5. Using a calendar table
The real power here is in how calendar tables simplify queries. Suppose we need to plan an event taking place on a Saturday in April 2020.
We can write a statement which returns these dates, but it's unlikely that it'll be as quick as writing the query shown.
By filtering the calendar table, the results give us exactly the dates we need. Furthermore, to avoid scheduling our event on the first day of Passover or the day before Easter Sunday, we can add additional filters in the WHERE clause. If we have a particular holiday in our calendar table, we can write a query to learn the date for that holiday.
6. Using a calendar table
With these additional holiday filters, we find that we do not want to schedule our event for the 11th. We still have three good dates.
7. A quick note on APPLY()
Before jumping into the exercises, let's talk about one of the most favored SQL Server operators: the APPLY() operator. At its simplest, APPLY() executes a function for each row in a result set, and performs at least as well as alternative techniques. A popular use of this operator is to simplify calculations.
In this query, we calculate the start of the fiscal year as July 1st and then use that date to calculate the fiscal day of year and fiscal week of year. There is much repetition in here, though, which can lead to subtle copy-paste errors.
8. A quick note on APPLY()
In the code shown, we use CROSS APPLY to apply a subquery dbo Calendar table. Specifically, the subquery calculates the start date of the fiscal year for the date in each row of the dbo Calendar table.
This variable is then referenced as fy and used to calculate FiscalDayOfYear and FiscalWeekOfYear.
There are other great uses for APPLY() but this is one of the best.
9. Let's practice!
Let's jump into some exercises!