vickps vickps - 1 month ago 9
SQL Question

SQL query to get the data grouped by weeks for the last 17 weeks

We are trying to group by week for last 17 weeks in SQL Server 2012.

Input data

OrderDate OrderValue
----------------------
7/17/11 10
7/24/11 20
7/31/11 30
8/7/11 40


SQL tried on it: I am not sure if it is heading into right direction.

Select
om.OrderDate, SUM(MOrderQty * MPrice) as OrderValue
from
OrdMaster om
inner join
OrdDetail od on om.SalesOrder = od.SalesOrder
where
om.OrderDate >= DATEADD(WEEK, -17,getdate())
group by
om.OrderDate


Output data:

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


Any help with this would be much appreciated ! Thanks in advance !

Answer

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)
;