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 (that is, the earliest value of c.Date in each group) 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. CustomerVisitStart is a DATETIME2 which includes time, so a direct join would only include visits starting at exactly midnight.
Group by the week of calendar year.

Take Hint (-30 XP)

Exercise

Downsample using a calendar table

Instructions