SQL Tutorial: Working with calendar tables

Key Takeaways

next we will look at one of the most powerful techniques available for working with dates and sequel 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 calendar year 2019 not all business processes work off a calendar year however some work off a fiscal year calendar tables let to 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 ecommerce 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 to find 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 dates 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 12th 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 favorites T sequel operators the apply operator at its simplest apply execute 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 1st and then use that date to calculate the fiscal day of here and fiscal week of year there is much repetition 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