## Running Total with Carry Forward

0

(click on this box to dismiss)

Q&A about the site for people studying math at any level and professionals in related fields

``````CREATE TABLE #Data (
Prod VARCHAR(20)
,SDate DATE
,OStock INT
,CStock INT
)

CREATE TABLE #BackData (
Prod VARCHAR(20)
,SDate DATE
,CStock INT
)

INSERT INTO #Data
SELECT 'p1', '2016-06-06', 10, 10
UNION ALL
SELECT 'p1', '2016-06-07', 10, 14
UNION ALL
SELECT 'p1', '2016-06-08', 14, 13
UNION ALL
SELECT 'p1', '2016-06-09', 13, 13
UNION ALL
SELECT 'p1', '2016-06-10', 13, 11

INSERT INTO #BackData
SELECT 'p1', '2016-06-06', 2
UNION ALL
SELECT 'p1', '2016-06-07', 4
UNION ALL
SELECT 'p1', '2016-06-09', -1
UNION ALL
SELECT 'p1', '2016-06-10', -2

;WITH ToUpdate AS (
SELECT d1.OStock, d1.CStock,
COALESCE(LAG(d2.CStock2) OVER (PARTITION BY d2.Prod
ORDER BY d2.SDate),
d1.OStock) AS OStock2,
d2.CStock2
FROM #Data AS d1
JOIN (
SELECT d.Prod, d.SDate, d.OStock, d.CStock,
COALESCE(t.newCStock,
LAG(t.newCStock) OVER (PARTITION BY d.Prod
ORDER BY d.SDate)) AS CStock2
FROM #Data AS d
LEFT JOIN (
SELECT bd.Prod, bd.SDate,
drn.CStock + SUM(bd.CStock) OVER (PARTITION BY bd.Prod
ORDER BY bd.SDate) AS newCStock
FROM #BackData AS bd
INNER JOIN (
SELECT Prod, CStock,
ROW_NUMBER() OVER (PARTITION BY Prod ORDER BY SDate) AS rn
FROM #Data
) AS drn ON bd.Prod = drn.Prod AND drn.rn = 1
) AS t ON t.Prod = d.Prod AND t.SDate = d.SDate
) AS d2 ON d1.Prod = d2.Prod AND d1.SDate= d2.SDate
)
UPDATE ToUpdate
SET OStock = OStock2,
CStock = CStock2

SELECT *
FROM #Data``````

### Enter Parameters

Options:
-
Switch sites:
Hold tight while we fetch your results
:records returned in :time ms:cached