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