We are trying to group by week for last 17 weeks in SQL Server 2012.
om.OrderDate, SUM(MOrderQty * MPrice) as OrderValue
OrdDetail od on om.SalesOrder = od.SalesOrder
om.OrderDate >= DATEADD(WEEK, -17,getdate())
OrderValue 7/17/11 7/24/11 7/31/11 8/7/11 8/14/11 8/21/11 8/28/11 9/4/11 9/11/11 9/18/11 9/25/11 10/2/11 10/9/11 10/16/11 10/23/11 10/30/11 11/6/11
First, I'll suggest that if you don't already have the following indexes on the two tables, consider adding them to support what we're getting ready to do and more.
--===== If you don't already have them, consider adding these indexes CREATE NONCLUSTERED INDEX By_OrderDate ON [dbo].OrdMaster([OrderDate]) INCLUDE ([SalesOrder]) ; CREATE NONCLUSTERED INDEX By_SalesOrder ON dbo.OrdDetail([SalesOrder]) INCLUDE ([MOrderQty],[MPrice]) ;
To make this all "auto-magical", we have to use a bit of dynamic SQL. It's called a "Pre-Aggregated Dynamic CROSSTAB", which is faster than most PIVOT operators. It will keep you from having to touch the code every week and it's nasty fast. I also took the liberty of adding a "Total" column. Details are in the code.
I also use "tokenized" dynamic SQL just to make things easier to code. It's not necessarily SQL Injection proof (have to use QUOTENAME a lot) but there's no chance of that here because of the datatypes being converted.
And, yes, this gives you the "horizontal format" that you were looking for.
--======================================================================================= -- Builds and executes a high performance, pre-aggregated CROSS TAB that will -- return the previous 17 weeks without having to adjust the code no matter -- what today's date is. It also returns a total for the 17 weeks. -- -- Note that if any give week has no sales, then you'll have bigger things to -- worry about other than this code won't pick up that missing week. ;-) -- We could fix that but it's not worth it because it shouldn't ever happen. --======================================================================================= --===== If the pre-aggregate table already exists, drop it to make reruns in SSMS easier. IF OBJECT_ID('tempdb..#PreAgg','U') IS NOT NULL DROP TABLE #PreAgg ; --===== Pre-aggregate the data into a working table. -- The right indexes will make this very fast and it greatly reduces the amount of -- work the CROSSTAB will have to do. SELECT WeekDate = CAST(DATEADD(dd,DATEDIFF(dd,-1,om.OrderDate)/7*7,-1) AS DATE) ,OrderValue = SUM(od.MOrderQty * od.MPrice) INTO #PreAgg FROM dbo.OrdMaster om JOIN dbo.OrdDetail od ON om.SalesOrder = od.SalesOrder WHERE om.OrderDate >= DATEADD(WK,-17,DATEADD(dd,DATEDIFF(dd,-1,GETDATE())/7*7,-1)) AND om.OrderDate < DATEADD(dd,DATEDIFF(dd,-1,GETDATE())/7*7,-1) GROUP BY DATEDIFF(dd,-1,om.OrderDate)/7*7 ; --===== Declare a place to build the dynamic SQL in. DECLARE @SQL VARCHAR(8000) ; --===== Create the dynamic SELECT list of the CROSSTAB from the preggregated table. SELECT @SQL = ISNULL(@SQL+SPACE(8)+',','') + REPLACE(REPLACE( '[<<WeekDate>>] = SUM(CASE WHEN WeekDate = "<<WeekDate>>" THEN OrderValue ELSE 0 END) ' ,'"' ,'''') --These are the other end of the replaces. ,'<<WeekDate>>',CONVERT(CHAR(8),WeekDate,1)) FROM #PreAgg ORDER BY WeekDate ; --===== Create the static parts of the dynamic CROSSTAB SQL and insert the dynamic part. SELECT @SQL = REPLACE(' SELECT <<@SQL>> ,[Total] = SUM(OrderValue) FROM #Preagg ;' ,'<<@SQL>>',@SQL) --The other end of the replace ; --===== Display the dynamic SQL for troubleshooting purposes. -- This can be commented out for production. PRINT @SQL ; --===== Execute the dynamic SQL EXEC (@SQL) ;