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

T-SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
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 ( [1],[2],[3],[4],[5],[6] ) ) pvt
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 ( [1],[2],[3],[4],[5],[6] ) ) 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.

*** Remember, if you have a SQL related question, try our Microsoft SQL Server Programming forum or our Microsoft SQL Server Admin forum