This recent MSDN thread presented an interesting, I believe, question:
For a predefined set of the Application IDs show the weekly transactions and display Monday’s day for each week. The interesting twist in this common PIVOT query is to display a Monday’s date for a week.
Rather than showing a solution for that particular thread, I show an AdventureWorks sample query. This query will return top 5 total amounts for each week by Customer (using SalesOrderHeader table).
Here is the query
DECLARE @First_Bow DATETIME, @Week_Start_Day TINYINT SET @Week_Start_Day = 2 SELECT @First_Bow = CONVERT(DATETIME,-53690 + ((@Week_Start_Day + 5)%7)) WITH cte AS (SELECT SH.CustomerID, TotalDue AS Total, DATEADD(DAY,(DATEDIFF(DAY,@FIRST_BOW,OrderDate) / 7) * 7, @FIRST_BOW) AS [MondayDate] FROM Sales.SalesOrderHeader SH), cte2 AS (SELECT Total, MondayDate, ROW_NUMBER() OVER(PARTITION BY MondayDate ORDER BY Total DESC, CustomerID) AS Row FROM cte) SELECT * FROM cte2 PIVOT (SUM(Total) FOR Row IN ( ,,,,, ) ) pvt
The interesting part here is in determining the Monday’s day for each week. I decided to use a solution presented in SQL Server Forums topic.