1

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?

0

1 Answer 1

1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.