Saturday, March 1, 2014

How to do cumulative substraction using T-Sql 2005

A sample for you:



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)
;


No comments:

Post a Comment