CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2012pivot
NEO
NEO

Reputation: 399

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 !

Upvotes: 0

Views: 172

Answers (2)

Jeff Moden
Jeff Moden

Reputation: 3494

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

Upvotes: 1

HoneyBadger
HoneyBadger

Reputation: 15140

Try:

Select datepart(week, 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 datepart(week, om.OrderDate))

See here.

Upvotes: 0

Related Questions