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
Other recent topics
