TSQL Rolling Query Help

Hi, 

I have 2 tables product and sales. The product table contains the productid (pid) and the lauchdate. Sales table contain the productid (pid), saledate, saleid and saleamount. 

The requirement is to get the 1) rolling 5 years sales of the product from the product launch date 2) Total Sales of all the products from the product launch date. 

I am getting the part 1) but I need help to get the sum of all the products for the launch dates of each product. 

Scripts :

CREATE TABLE #product
( pid INT, 
  launchdate DATETIME)
CREATE TABLE #sales 
  (saleid INT, 
   pid INT, 
   saleamount INT, 
   saledate DATETIME )

INSERT #product 
           ( pid, launchdate )
   VALUES  ( 1, -- pid - int
             '2009-04-01' -- launchdate - datetime
             )
INSERT #product 
           ( pid, launchdate )
   VALUES  ( 2, -- pid - int
             '2009-08-01' -- launchdate - datetime
             )
INSERT #product 
           ( pid, launchdate )
   VALUES  ( 3, -- pid - int
             '2010-02-01' -- launchdate - datetime
             )
 SELECT * FROM #product 

 TRUNCATE TABLE #sales

 SELECT * FROM 	 #sales 	  		  INSERT #sales
        ( saleid, pid, saleamount, saledate )
VALUES  ( 110, -- saleid - int
          1, -- pid - int
          5, -- saleamount - int
          '2014-02-01'  -- saledate - datetime
          ) 

INSERT #sales
        ( saleid, pid, saleamount, saledate )
VALUES  ( 101, -- saleid - int
          1, -- pid - int
          5, -- saleamount - int
          '2009-05-01'  -- saledate - datetime
          ) 

INSERT #sales
        ( saleid, pid, saleamount, saledate )
VALUES  ( 102, -- saleid - int
          1, -- pid - int
          5, -- saleamount - int
          '2009-07-01'  -- saledate - datetime
          ) 

INSERT #sales
        ( saleid, pid, saleamount, saledate )
VALUES  ( 103, -- saleid - int
          1, -- pid - int
          5, -- saleamount - int
          '2010-01-01'  -- saledate - datetime
          ) 

INSERT #sales
        ( saleid, pid, saleamount, saledate )
VALUES  ( 104, -- saleid - int
          1, -- pid - int
          5, -- saleamount - int
          '2010-05-01'  -- saledate - datetime
          ) 

INSERT #sales
        ( saleid, pid, saleamount, saledate )
VALUES  ( 105, -- saleid - int
          1, -- pid - int
          5, -- saleamount - int
          '2011-05-01'  -- saledate - datetime
          ) 

INSERT #sales
        ( saleid, pid, saleamount, saledate )
VALUES  ( 106, -- saleid - int
          1, -- pid - int
          5, -- saleamount - int
          '2011-06-01'  -- saledate - datetime
          ) 

INSERT #sales
        ( saleid, pid, saleamount, saledate )
VALUES  ( 107, -- saleid - int
          1, -- pid - int
          5, -- saleamount - int
          '2012-06-01'  -- saledate - datetime
          ) 

		  INSERT #sales
        ( saleid, pid, saleamount, saledate )
VALUES  ( 108, -- saleid - int
          1, -- pid - int
          5, -- saleamount - int
          '2012-08-01'  -- saledate - datetime
          ) 

		  		  INSERT #sales
        ( saleid, pid, saleamount, saledate )
VALUES  ( 109, -- saleid - int
          1, -- pid - int
          5, -- saleamount - int
          '2013-08-01'  -- saledate - datetime
          ) 

		  
		  		  INSERT #sales
        ( saleid, pid, saleamount, saledate )
VALUES  ( 110, -- saleid - int
          1, -- pid - int
          5, -- saleamount - int
          '2013-06-01'  -- saledate - datetime
          ) 

		  	  		  INSERT #sales
        ( saleid, pid, saleamount, saledate )
VALUES  ( 110, -- saleid - int
          1, -- pid - int
          5, -- saleamount - int
          '2014-02-01'  -- saledate - datetime
          ) 

		  	  	  		  INSERT #sales
        ( saleid, pid, saleamount, saledate )
VALUES  ( 111, -- saleid - int
          1, -- pid - int
          5, -- saleamount - int
          '2014-01-01'  -- saledate - datetime
          ) 


		  /*pid 2*/


INSERT #sales
        ( saleid, pid, saleamount, saledate )
VALUES  ( 112, -- saleid - int
          2, -- pid - int
          10, -- saleamount - int
          '2009-12-01'  -- saledate - datetime
          ) 

INSERT #sales
        ( saleid, pid, saleamount, saledate )
VALUES  ( 113, -- saleid - int
          2, -- pid - int
          10, -- saleamount - int
          '2010-07-01'  -- saledate - datetime
          ) 

INSERT #sales
        ( saleid, pid, saleamount, saledate )
VALUES  ( 114, -- saleid - int
          2, -- pid - int
          10, -- saleamount - int
          '2010-03-01'  -- saledate - datetime
          ) 

INSERT #sales
        ( saleid, pid, saleamount, saledate )
VALUES  ( 115, -- saleid - int
          2, -- pid - int
          10, -- saleamount - int
          '2010-08-01'  -- saledate - datetime
          ) 

INSERT #sales
        ( saleid, pid, saleamount, saledate )
VALUES  ( 116, -- saleid - int
          2, -- pid - int
          10, -- saleamount - int
          '2011-04-01'  -- saledate - datetime
          ) 

INSERT #sales
        ( saleid, pid, saleamount, saledate )
VALUES  ( 117, -- saleid - int
          2, -- pid - int
          10, -- saleamount - int
          '2011-09-01'  -- saledate - datetime
          ) 

INSERT #sales
        ( saleid, pid, saleamount, saledate )
VALUES  ( 118, -- saleid - int
          2, -- pid - int
          10, -- saleamount - int
          '2012-03-01'  -- saledate - datetime
          ) 

		  INSERT #sales
        ( saleid, pid, saleamount, saledate )
VALUES  ( 119, -- saleid - int
          2, -- pid - int
          10, -- saleamount - int
          '2012-08-01'  -- saledate - datetime
          ) 

		  		  INSERT #sales
        ( saleid, pid, saleamount, saledate )
VALUES  ( 120, -- saleid - int
          2, -- pid - int
          10, -- saleamount - int
          '2013-04-01'  -- saledate - datetime
          ) 

		  
		  		  INSERT #sales
        ( saleid, pid, saleamount, saledate )
VALUES  ( 121, -- saleid - int
          2, -- pid - int
          10, -- saleamount - int
          '2013-08-01'  -- saledate - datetime
          ) 

		  	  		  INSERT #sales
        ( saleid, pid, saleamount, saledate )
VALUES  ( 122, -- saleid - int
          2, -- pid - int
          10, -- saleamount - int
          '2014-02-01'  -- saledate - datetime
          ) 

		  	  	  		  INSERT #sales
        ( saleid, pid, saleamount, saledate )
VALUES  ( 123, -- saleid - int
          2, -- pid - int
          10, -- saleamount - int
          '2014-01-01'  -- saledate - datetime
          )

Here is my query for Part 1)

	  SELECT 
		   a.pid, 
		    pp.Launchdate,
		  SUM(SaleYear1) SaleYear1,
		--(SELECT SUM(s.saleamount) FROM #sales s WHERE s.saledate >=pp.launchdate AND s.saledate<DATEADD(yy, 1, pp.launchdate) ) AS TotalSaleYear1,
		  sum(SaleYear2) SaleYear2,
		  --(SELECT SUM(s.saleamount) FROM #sales s WHERE s.saledate >=DATEADD(yy, 1, pp.launchdate) AND s.saledate<DATEADD(yy, 2, pp.launchdate) ) AS TotalSaleYear2,
		  sum(SaleYear3) SaleYear3,
		  sum(SaleYear4)  SaleYear4,
		  sum(SaleYear5) SaleYear5
          FROM (

		  SELECT p.pid,YEAR(p.launchdate) AS Launchdate,s.saleid,
		  CASE 
		  WHEN s.saledate >=p.launchdate AND s.saledate<DATEADD(yy, 1, p.launchdate) THEN s.saleamount END AS SaleYear1,
		  CASE 
		  WHEN s.saledate >=DATEADD(yy, 1, p.launchdate) AND s.saledate<DATEADD(yy, 2, p.launchdate) THEN s.saleamount END AS SaleYear2,
		  CASE 
		  WHEN s.saledate >=DATEADD(yy, 2, p.launchdate)  AND s.saledate<DATEADD(yy, 3, p.launchdate) THEN s.saleamount END AS SaleYear3,
		  CASE 
		  WHEN s.saledate >=DATEADD(yy, 3, p.launchdate) AND s.saledate<DATEADD(yy, 4, p.launchdate) THEN s.saleamount END AS SaleYear4,
		  CASE 
		  WHEN s.saledate >=DATEADD(yy, 4, p.launchdate) AND s.saledate<DATEADD(yy, 5, p.launchdate) THEN s.saleamount END AS SaleYear5
		  FROM #product p
		  JOIN #sales s ON p.pid =s.pid
		  --WHERE p.pid=1
		  
		   ) AS a 
		   JOIN #product pp ON pp.pid= a.pid

		  GROUP BY a.pid, pp.Launchdate

Output I get currently 

Need Help for Part 2) Columns : TotalSalesYear1, TotalSalesYear2, TotalSalesYear3,TotalSalesYear4, TotalSalesYear5 


TotalSalesYear1= calculated as sum of sales of all products ( including pid=1) for saledate >=launchdate and sale< launchdate+1year 

Need something like this for all products :

Please advice

Thank you 


August 20th, 2015 1:57am
Hi eve05,

Please check below sample.

;WITH Cte AS --generate five year period for each product
(
SELECT pid, DATEADD(YY,c.n-1,launchdate) startDT ,DATEADD(YY,c.n,launchdate) endDT,n FROM #product p
CROSS APPLY
(VALUES(1),(2),(3),(4),(5)) C(n)
),
Cte2 AS
(
SELECT c.pid,n,SUM(saleAmount) sales FROM Cte c INNER JOIN #sales s ON c.pid=s.pid and s.saledate>=c.startDT and s.saledate < c.endDT
GROUP BY c.pid,n
)
SELECT pid,[1] SalesY1,TotalSalesY1,[2] SalesY2,TotalSalesY2,
	   [3] SalesY3,TotalSalesY3,[4] SalesY4,TotalSalesY4,[5] SalesY5,TotalSalesY5 FROM Cte2 
PIVOT
(
MAX(sales) FOR n IN([1],[2],[3],[4],[5])
)P
CROSS APPLY
(
SELECT 
SUM(CASE WHEN n= 1 THEN sales ELSE 0 END) TotalSalesY1,
SUM(CASE WHEN n= 2 THEN sales ELSE 0 END) TotalSalesY2,
SUM(CASE WHEN n= 3 THEN sales ELSE 0 END) TotalSalesY3,
SUM(CASE WHEN n= 4 THEN sales ELSE 0 END) TotalSalesY4,
SUM(CASE WHEN n= 5 THEN sales ELSE 0 END) TotalSalesY5
FROM Cte2
)c


Free Windows Admin Tool Kit Click here and download it now
August 20th, 2015 3:48am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics