sql - How do I have a running sum count/calculation? -
sql - How do I have a running sum count/calculation? -
i have found myself in challenging scenario cannot figure out. have table "#pricechange" need figure out various turn a profit losses, between 2 dates, based on cost change
example:
product 1001 (black jeans), has alter cost 3 times.
2014-11-02: 10.99 > 8.99 (50 sold) 2014-11-03: 8.99 > 4.99 (25 sold)i need calculate, losses based on original price.
along lines of:
(10.99 - 8.99) = $2, $2 x 50 = $100 loss (8.99 - 4.99) = $4, $4 x 25 = $100 loss.total loss = $200.
any help appreciated.
create table #pricechange ( product int, description varchar(30), validfrom date, validto date, currentprice decimal (5,2), soldbetweenvaliddates int ); insert #pricechange values (1001,'black jeans','2014-11-01','2014-11-01', 10.99, 100); insert #pricechange values (1001,'black jeans','2014-11-02','2014-11-02', 8.99, 50); insert #pricechange values (1001,'black jeans','2014-11-03',null, 4.99, 25); insert #pricechange values (1002,'shirt','2014-11-01','2014-11-01', 10.99, 100); insert #pricechange values (1002,'shirt','2014-11-02','2014-11-02', 8.99, 50); insert #pricechange values (1002,'shirt','2014-11-03',null, 4.99, 25); select * #pricechange drop table #pricechange
with cteprice ( select *, row_number() over(partition product order validfrom) rnk #pricechange) select p1.product, sum((p1.currentprice - p2.currentprice)* p2.soldbetweenvaliddates) cteprice p1 bring together cteprice p2 on p1.rnk + 1 = p2.rnk , p1.product = p2.product grouping p1.product
sql sql-server sql-server-2008-r2
Comments
Post a Comment