SQL Tutorial: Working with calendar tables

Pubblicato il: 14 marzo 2020
sul canale di: DataCamp

7,447

Want to learn more? Take the full course at https://learn.datacamp.com/courses/ti... at your own pace. More than a video, you'll learn hands-on coding & quickly apply skills to your daily work.

---
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 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 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 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 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 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 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.

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.

#DataCamp #SQLTutorial #SQL #Building #dates #TimeSeries #Analysis #SQLServer #calendartables

In questa pagina del sito puoi guardare il video online SQL Tutorial: Working with calendar tables della durata di ore minuti seconda in buona qualità , che l'utente ha caricato DataCamp 14 marzo 2020, condividi il link con amici e conoscenti, su youtube questo video è già stato visto 7,447 volte e gli è piaciuto 40 spettatori. Buona visione!

1,535