Downsample using a calendar table

Management liked the weekly report but they wanted to see every week in 2020, not just the weeks with amenity usage. We can use a calendar table to solve this problem: the calendar table includes all of the weeks, so we can join it to the dbo.DaySpaVisit table to find our answers.

Management would also like to see the first day of each calendar week, as that provides important context to report viewers.

Find and include the week of the calendar year.
Include the first date in the calendar week and as FirstDateOfWeek.
Join the Calendar table to the DaySpayVisit table based on the calendar table's date and each day spa customer's date of visit. Remember that CustomerVisitStart is a DATETIME2 which includes time, so a direct join would only include visits starting at exactly midnight.
Include the week of calendar year in the GROUP BY clause.

Take Hint (-30 XP)

Exercise

Downsample using a calendar table

Instructions