XML FeedsThis 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 ( [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
LTD Social Sitings
Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.