T SQL Cursor Update field based on previous field

Question

I'm using SQL Server 2016.

I have this table:

RowID     SKU     Shop    Week   Prioirty   Replen    Open_Stk 
---------------------------------------------------------------
 1        111     100      1        1        400         5000           
 2        111     200      1        2        400         NULL
 3        111     300      1        3        400         NULL
 4        111     400      1        4        400         NULL

This is the desired result:

RowID     SKU     Shop    Week   Prioirty   Replen    Open_Stk 
---------------------------------------------------------------
 1        111     100      1        1        400         5000           
 2        111     200      1        2        400         4600
 3        111     300      1        3        400         4200
 4        111     400      1        4        400         3800

The calculation for Open_Stk is based on the previous row:

[Open_Stk] = [Open_Stk]-IIF([Replen]<=IIF([Open_Stk]>=0,[Open_Stk],0),[Replen],0)

I am using the below cursor to update the Open_Stk but nothing happens - what am I missing:

DECLARE @CurrentRow INT;
DECLARE @PreviousRow INT

DECLARE ShopRank CURSOR FOR
    SELECT RowID
    FROM [tmp_tblTEST]
    ORDER BY [SKU], [Week],Priority

OPEN ShopRank
FETCH NEXT FROM ShopRank INTO @CurrentRow

WHILE @@FETCH_STATUS = 0
BEGIN 
    IF ((SELECT [Open_Stk] FROM [tmp_tblTEST] WHERE RowID = @CurrentRow) IS NULL)
    BEGIN
        UPDATE [tmp_tblTEST]
        SET [Open_Stk] = [Open_Stk] - IIF([Replen] <= IIF([Open_Stk] >= 0, [Open_Stk], 0), [Replen], 0) 
        WHERE RowID = @PreviousRow
    END

    SET @PreviousRow = @CurrentRow

    FETCH NEXT FROM ShopRank INTO @CurrentRow
END

CLOSE ShopRank
DEALLOCATE ShopRank

Answer 1

WITH result AS 
( 
    SELECT
        a.*, ISNULL(NULLIF(a.Open_Stk, 0), 0) AS Output 
    FROM
        table1 a
    JOIN
        table1 b ON a.Prioirty = b.Prioirty - 1

    UNION ALL 

    SELECT 
        a.*, output - a.Replen
    FROM
        table1 a
    JOIN
        result b ON a.Prioirty = b.Prioirty+1 
) 
SELECT * 
FROM result
WHERE output > 0

Answer 2

There's no need for a CURSOR here at all. This is a little bit of guess work, but I suspect what you are actually after here is something like this:

SELECT V.RowID,
       V.SKU,
       V.Shop,
       V.[Week],
       V.Priority,
       V.Replen,
       FIRST_VALUE(V.Open_Stk) OVER (PARTITION BY V.SKU ORDER BY V.[Week], V.Priority
                                     ROWS UNBOUNDED PRECEDING) -
       ISNULL(SUM(V.Replen) OVER (PARTITION BY V.SKU ORDER BY V.[Week], V.Priority
                                  ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING),0) AS OpenStk
FROM (VALUES (1,111,100,1,1,400,5000),           
             (2,111,200,1,2,400,NULL),
             (3,111,300,1,3,400,NULL),
             (4,111,400,1,4,400,NULL))V(RowID,SKU,Shop,[Week],Priority,Replen,Open_Stk)
ORDER BY V.Sku,
         V.[Week],
         V.Priority;

DB<>Fiddle (using original solution)

FIRST_VALUE does what is says on the tin. The SUM subtracts the values from every prior row from the value of Open_Stk on the first row; making the final result set. It only references the prior rows due to the ROWS BETWEEN clause. ROWS UNBOUNDED means to start at the beginning of the partitioned range, and 1 PRECEDING means the row prior.