Running Total (deduct value) (T-SQL)



DECLARE @Products TABLE (ProductID INT, ProductName VARCHAR(200), Price int)
 
INSERT INTO @Products(ProductID, ProductName,Price)
VALUES ( 1, 'A', 1000),(2, 'B',500),(3, 'C', 200),(4, 'E', 300)
 

 ;with mycte1 as (
select ProductID, ProductName, row_number() Over(Order by ProductID)  rn,Price
 
from @Products)
 
, mycte2 AS
(
 SELECT ProductID, ProductName, rn, Price
 FROM mycte1
 WHERE rn = 1
 UNION ALL
 SELECT n.ProductID, n.ProductName, n.rn,  (m.Price - n.Price) as price 
 FROM mycte2 m INNER JOIN mycte1 n
 ON n.rn = m.rn + 1
)
SELECT ProductID, ProductName,Price FROM mycte2
 ORDER BY ProductID
 
--OPTION (MAXRECURSION 0)
;



 
Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s