Sql-server – T SQL Cte Previous record calcuation

ctesql servert-sql

I'm using SQL Server 2016.

I have the below table:

SKU     Shop       Week    ShopPrioirty    Replen      Open_Stk  Open_Stk Calc
111     100         1            1           0           17         NULL
111     200         1            2           2           NULL       NULL 
111     300         1            3           0           NULL       NULL
111     400         1            4           0           NULL       NULL

222     100         2            1           5          17          NULL 
222     200         2            2           5          NULL        NULL
222     300         2            3           5          NULL        NULL
222     400         2            4           5          NULL        NULL

This is the desired result:

SKU     Shop      Week    ShopPrioirty    Replen      Open_Stk  Open_Stk Calc
111     100         1            1           0           17         17
111     200         1            2           2          NULL        17
111     300         1            3           0          NULL        15
111     400         1            4           0          NULL        15

222     100         2            1           20           17        17
222     200         2            2           15          NULL       12
222     300         2            3           12          NULL        7
222     400         2            4           10          NULL        2

I need to update the 'Open_Stk Calc' based on the previous row:

'Open_Stk Calc' - IIF('Replen'<=IIF('Open_Stk'>=0,'Open_Stk',0),'Replen',0)

I am using a CTE to update a row based on a calculation of the previous rows. This is my SQL:

    ;WITH CTE AS 
    (
        SELECT
        SKU,
        [Shop],
        [Week],
        [Store_Priority],
        [Replen],      
        [Open_Stk],
        [Open_Stk Calc],
        FIRST_VALUE([Open_Stk]) OVER ( PARTITION BY [SKU] ,[Week] ORDER BY [Store_Priority] ROWS UNBOUNDED PRECEDING) 
        - 
ISNULL(SUM(IIF([Replen] <= IIF([Open_Stk]>=0,[Open_Stk],0),[Replen],0))
        OVER (PARTITION BY [SKU] ,[Week] ORDER BY [Store_Priority] ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), 0) AS CurrentStock
    FROM [tblTEST])

    UPDATE CTE 
    SET [Open_Stk Calc] = CurrentStock

However, this produces the following result:

SKU     Shop       Week    ShopPrioirty    Replen      Open_Stk  Open_Stk Calc
111     100         1            1           0           17         17
111     200         1            2           2           NULL       17 
111     300         1            3           0           NULL       17
111     400         1            4           0           NULL       17

And not the desired result – where have I gone wrong?

Best Answer

Test something like:

Open_Stk Calc = SUM(COALESCE(Open_Stk + Replen, 0)) OVER (PARTITION BY sku ORDER BY ShopPrioirty)

Or divide to two SUMs to avoid COALESCE() usage. Edit PARTITION expression if needed. - akina

Related Question