SQL Server - Running Total with Carry Forward

Question

Needs some help on the following:

Table #Data contains the Opening and Closing Stock for a product over 5 days

Table #BackData contains some post dated transactions

How can i Update the table #Data with a Running Total including a carry forward

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

DROP TABLE #Data
DROP TABLE #BackData

Desired Output :

Prod|   SDate   |OStock |CStock|
p1  |2016-06-06 |10     |12    |
p1  |2016-06-07 |12     |16    |
p1  |2016-06-08 |16     |16    |
p1  |2016-06-09 |16     |15    |
p1  |2016-06-10 |15     |13    |

EDIT

This is what i had managed to write before i got the answer, using two updates because the actual table had too many columns to use in a single query.

UPDATE D
SET D.CStock = FL.NewCStock
FROM #Data D
INNER JOIN (
    SELECT DT.Prod
        ,DT.SDate
        ,SUM(IIF(RwNm = 1, DT.CStock, 0) + ISNULL(BD.CStock, 0)) OVER (
            PARTITION BY DT.Prod ORDER BY DT.SDate ROWS UNBOUNDED PRECEDING
            ) NewCStock
    FROM (
        SELECT Prod
            ,SDate
            ,CStock
            ,ROW_NUMBER() OVER (
                PARTITION BY Prod ORDER BY SDate
                ) AS RwNm
        FROM #Data
        ) DT
    LEFT JOIN #BackData BD ON DT.Prod = DT.Prod
        AND BD.SDate = DT.SDate
    ) FL ON D.Prod = FL.Prod
    AND D.SDate = FL.SDate

UPDATE D
SET D.OStock = PV.NewOStock
FROM #Data D
INNER JOIN (
    SELECT Prod
        ,SDate
        ,ISNULL(LAG(CStock) OVER (
                PARTITION BY Prod ORDER BY SDate
                ), CStock) AS NewOStock
    FROM #Data
    ) PV ON D.Prod = PV.Prod
    AND D.SDate = PV.SDate

can you explain how you got this two record `p1 |2016-06-07 |12 |16` / `p1 |2016-06-08 |16 |16` ? — Sufyan Jabr, Jun 12 '16 at 09:00
How do you derived at the desired output for `2016-06-07` onwards ? — Squirrel, Jun 12 '16 at 09:24
@SufyanJabr the closing of previous day is the opening for the next day, thus CStock for 2016-06-06 becomes the OStock for 2016-06-07 adding the post date transaction of 4 results in 16. Because there are no post date transactions for 2016-06-08 the same results carry forward. — Romil Maheshwari, Jun 12 '16 at 12:44
@Squirrel please refer my previous comment for the calculation reason — Romil Maheshwari, Jun 12 '16 at 12:44
i assumed with #Data you already have some transactions in there. That's why the Opening stock is differnet from Closing stock for a Day. So with the back-dated transactions coming in, It should be just add on to the transaction. It seems that you are re-calculating all the Opening & Closing bal for dates 2016-06-07 onwards based solely on the #BackData — Squirrel, Jun 12 '16 at 13:12

Giorgos Betsos · Accepted Answer · 2016-06-12T08:58:06.940

You can use the following query to UPDATE:

;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

This looks awfully convoluted, but I couldn't think of anything simpler.

Demo here

i managed to do something similar, however i was using two updates. one to calculate the closing stock and then running a LAG to update the opening stock. — Romil Maheshwari, Jun 12 '16 at 13:09
@Romil Glad I was able to help and welcome to Stack Overflow. Please mark this or any other answer as accepted if it helped you solve your problem. — Giorgos Betsos, Jun 12 '16 at 14:01

gofr1 · Answer 2 · 2016-06-12T09:54:01.383

You can rebuild values in #Data table with the help of recursive CTE:

;WITH cte AS (
SELECT top 1 d.Prod,
        d.SDate,
        d.OStock,
        d.OStock + b.CStock as CStock
FROM #Data d
LEFT JOIN #BackData b
    ON b.Prod = d.Prod and b.SDate = d.SDate
ORDER BY d.SDate ASC
UNION ALL
SELECT  c.Prod,
        DATEADD(day,1,c.SDate),
        c.CStock,
        c.CStock + ISNULL(b.CStock,0)
FROM cte c
INNER JOIN #Data d 
    ON d.Prod = c.Prod AND d.SDate = DATEADD(day,1,c.SDate)
OUTER APPLY (SELECT CStock FROM #BackData b WHERE b.Prod = d.Prod and b.SDate = d.SDate) as b
)

SELECT *
FROM cte

Output:

Prod                 SDate      OStock      CStock
-------------------- ---------- ----------- -----------
p1                   2016-06-06 10          12
p1                   2016-06-07 12          16
p1                   2016-06-08 16          16
p1                   2016-06-09 16          15
p1                   2016-06-10 15          13

To update #Data:

UPDATE d
SET OStock = c.OStock, CStock = c.CStock
FROM #Data d
INNER JOIN cte c
    ON c.Prod = d.Prod AND c.SDate = d.SDate

Squirrel · Answer 3 · 2016-06-12T10:02:56.603

Shouldn't the result be like :

Prod SDate      OStock CStock
p1   2016-06-06     10     12
p1   2016-06-07     12     20 (#Data CStock 14 + #BakData 2 + 4)
p1   2016-06-08     20     19 (#Data CStock 13 + #BakData 2 + 4)
p1   2016-06-09     19     18 (#Data CStock 13 + #BakData 2 + 4 - 1)
p1   2016-06-10     18     14 (#Data CStock 11 + #BakData 2 + 4 - 1 -2)

This query will produce the above result

update  d
set OStock  = d.OStock + a.OAdj,
    CStock  = d.CStock + a.CAdj
from    #Data d
    cross apply
    (
        select  OAdj  = sum(case when Oflag = 1 then x.CStock else 0 end),
            CAdj =  sum(x.CStock)
        from    
        (
            select  *, Oflag = case when x.SDate = d.SDate then 0 else 1 end
            from    #BackData x
            where   x.Prod      = d.Prod 
            and x.SDate     <= d.SDate
        ) x
    ) a

score 0 · Answer 4 · answered Jun 12 '16 at 10:21

Based on your expected output, it seems that you are re-calculating the daily Opening / Closing balance based on the figure from 2016-06-06

Here is a solution that will gives you your expected output.

; with 
cte as
(
    select  Prod, SDate, OStock, CStock, 
            rn = row_number() over (partition by Prod order by SDate)
    from    #Data
),
adj as
(
    select  Prod, SDate, CStock
    from    cte
    where   rn  = 1
    union all
    select  Prod, SDate, CStock
    from    #BackData
)
update  d
set OStock  = coalesce(o.OStock, d.OStock),
    CStock  = c.CStock
from    #Data d
    cross apply
    (
        select  OStock = sum(x.CStock)
        from    adj x
        where   x.Prod  = d.Prod
        and x.SDate < d.SDate
    ) o
    cross apply
    (
        select  CStock = sum(x.CStock)
        from    adj x
        where   x.Prod  = d.Prod
        and x.SDate <= d.SDate
    ) c

Result :

p1  2016-06-06  10  12
p1  2016-06-07  12  16
p1  2016-06-08  16  16
p1  2016-06-09  16  15
p1  2016-06-10  15  13

SQL Server - Running Total with Carry Forward

4 Answers4