End of Summer - hours only!Up to 80% off on all courses and bundles.-Close
Introduction
Customer registration
Customer cohorts
Summary

Instruction

Great! You may have noticed that displaying the week number is not very readable. After all, when does week 22 start? Is it in April, in May, or in June? If you'd like to see the week label as a date, the quick and dirty trick to solve this problem is to display the minimum registration date in a week.

SELECT
  DATEPART(year, RegistrationDate) AS RegistrationYear,
  DATEPART(week, RegistrationDate) AS RegistrationWeek,
  MIN(RegistrationDate) AS WeekLabel,
  COUNT(CustomerID) AS RegistrationCount
FROM Customers
GROUP BY DATEPART(year, RegistrationDate), DATEPART(week, RegistrationDate)
ORDER BY DATEPART(year, RegistrationDate), DATEPART(week, RegistrationDate);

This query will show the year and week number, and the earliest date in this week. This solution is not perfect: if there are no registrations on Monday, then you'll see Tuesday's date as the week label for this week. The code for displaying the week label correctly is more complex. We've described this in the article: How to Get the First Day of the Week in SQL Server.

And if you want to know more on how weeks work in SQL Server, check out this article: How to Group Data by Week in SQL Server.

Exercise

The template shows the solution for the previous exercise. Add a new column WeekLabel displaying the minimum registration date in each week. Is the result more readable now?

Stuck? Here's a hint!

Just add a new column in the query:

MIN(RegistrationDate) AS WeekLabel