Richie CottonSenior Data Evangelist, DataCamp · Ask me about data and AI upskilling

SQL Tutorial: Working with calendar tables

Name: SQL Tutorial: Working with calendar tables
Uploaded: 2020-03-14T12:14:32+00:00
Duration: 4 min 7 s
Channel: Richie Cotton
Description: In this tutorial, the presenter explains the concept and construction of a calendar (date dimension) table in SQL Server and demonstrates how it…

14 Mar 2020 · 4 min

Watch on YouTube

4:07

In this tutorial, the presenter explains the concept and construction of a calendar (date dimension) table in SQL Server and demonstrates how it simplifies date‑related queries. The talk begins by defining a calendar table as a static, wide table that stores a date key, the date itself, and supporting attributes. Column categories are outlined: general columns (date, day name, weekend flag), calendar‑year attributes (quarter, year), fiscal‑year attributes (fiscal year, fiscal quarter, fiscal day/week), and specialized columns such as holidays, lunar dates, or other business‑specific markers. The presenter advises building the table once, treating it as a warehouse dimension, and leveraging built‑in date functions or existing scripts rather than reinventing the wheel. Practical query examples illustrate how the table makes it easy to retrieve specific days—e.g., all Saturdays in April 2020—and to exclude dates that coincide with holidays like Passover or Easter. The tutorial then introduces the T‑SQL APPLY operator, describing it as a row‑by‑row function executor that often outperforms alternative methods. An example shows using APPLY to compute the fiscal year start (July 1) and then derive fiscal day‑of‑year and fiscal week‑of‑year, with a suggestion to encapsulate the fiscal‑year start logic in an ad‑hoc function to reduce repetition and errors. The session concludes by directing learners to hands‑on exercises.

Chapters

Next, we will look at one of the most powerful techniques available for working with dates in SQL Server, the Calendar Table A calendar table is a table which stores date information for easy retrieval. Here is a calendar table with some of its columns. We have an integer key which represents the date, the date itself, and supporting information for that day. A calendar table can hold lots of interesting data and can be broken down into categories. First, we have general columns, including columns like the 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 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 and where they differ. Finally, there are specialized columns which might be important for your business but might not be important everywhere for e commerce sites. Some holidays are critical, so we want to store those holidays. We might also store information about lunar calendars or other entries. This is a table you build once and ideally never update. So treat it like a warehouse dimension and make it as wide as you want. Building a calendar table is straightforward. Define the columns you need. Here are a few sample columns for each day we want to include. We can use the built in functions to get most of the way there. Don't reinvent the wheel for your calendar table. Look for date dimensions or calendar tables online and adapt one of those scripts. The real power here is in how calendar tables simplify queries. Suppose I need to plan an event and it should take place on a Saturday in April 2020. We can write a statement which returns these dates, but it won't be as simple as this query. The results give us back exactly the dates we need. Furthermore, if we want to avoid scheduling our event on the first day of Passover or the day before Easter Sunday, we can do this with our calendar table too. If we have that holiday in our calendar table, we can write a query to learn that easter is on April 12, 2020. Therefore, we do not want to schedule our event for the 11th. We still have three good dates. Before jumping into the exercises, let's talk about one of my favorite T SQL operators, the Apply operator. At its simplest, Apply executes a function for each row in a result set, but it does so in a way which typically performs at least as well as alternative techniques we will use Apply regularly. One of my favorite uses of this operator is to simplify calculations. In this query, we calculate the start of the fiscal year as July 1, 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. By moving fiscal year start into its own ad hoc function, we can reference it as a calculation for the other two variables, making our query much simpler. There are other great uses for Apply, but this is one of the best. Now that you've had a chance to see calendar tables and the Apply operator in action, let's jump to some exercises and work with them.