none
T SQL CTE Previous Row Calculation RRS feed

  • Question

  • 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?
    Friday, June 14, 2019 10:38 AM

Answers

  • Hi,

    Please try this query:

    CREATE TABLE [tblTEST] (
        SKU INT,
        Shop INT,
        Week INT,
        ShopPrioirty INT,
        Replen INT,
        Open_Stk INT,
        Open_Stk_Calc INT
        );
    INSERT INTO dbo.tblTEST
    VALUES
     (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);
    
    
    WITH cte
    AS (
        SELECT t1.sku,t1.shop,t1.week,t1.ShopPrioirty,t1.Replen,t1.Open_Stk,t1.Open_Stk_Calc,
                SUM(t2.Open_Stk) OVER (PARTITION BY T1.SKU,t1.shop) total_stk,
                SUM(t2.Replen) OVER (PARTITION BY T1.SKU,t1.shop) total_Replen,
                iif((SUM(t2.Open_Stk) OVER (PARTITION BY T1.SKU,t1.shop) - SUM(t2.Replen) OVER (PARTITION BY T1.SKU,t1.shop)) > 0
                    , SUM(t2.Open_Stk) OVER (PARTITION BY T1.SKU,t1.shop) - SUM(t2.Replen) OVER (PARTITION BY T1.SKU,t1.shop)
                    , NULL) Calc
        FROM tbltest t1
        INNER JOIN tbltest t2 ON t1.sku = t2.sku AND t1.ShopPrioirty >= t2.ShopPrioirty
        ),
    cte2
    AS (
        SELECT *,
            min(Calc) OVER (PARTITION BY sku ORDER BY ShopPrioirty) min_calc,
            lag(total_stk - total_Replen) OVER (PARTITION BY sku ORDER BY ShopPrioirty) lag_calc
        FROM cte
        ),
    cte3
    AS (
        SELECT sku,shop,week,ShopPrioirty,Replen,Open_Stk,Open_Stk_Calc,
            iif(max(min_calc) >= max(isnull(lag_calc, 0)), max(min_calc), max(lag_calc)) new_Open_Stk_Calc
        FROM cte2
        GROUP BY sku,shop,week,ShopPrioirty,Replen,Open_Stk,Open_Stk_Calc
        )
    UPDATE t
    SET t.Open_Stk_Calc = c.new_Open_Stk_Calc
    FROM cte3 c
    INNER JOIN tblTest t ON c.sku = t.sku
        AND c.shop = t.shop;

    Output of table after update:


    Friday, June 14, 2019 6:52 PM

All replies

  • Hi,

    Please try this query:

    CREATE TABLE [tblTEST] (
        SKU INT,
        Shop INT,
        Week INT,
        ShopPrioirty INT,
        Replen INT,
        Open_Stk INT,
        Open_Stk_Calc INT
        );
    INSERT INTO dbo.tblTEST
    VALUES
     (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);
    
    
    WITH cte
    AS (
        SELECT t1.sku,t1.shop,t1.week,t1.ShopPrioirty,t1.Replen,t1.Open_Stk,t1.Open_Stk_Calc,
                SUM(t2.Open_Stk) OVER (PARTITION BY T1.SKU,t1.shop) total_stk,
                SUM(t2.Replen) OVER (PARTITION BY T1.SKU,t1.shop) total_Replen,
                iif((SUM(t2.Open_Stk) OVER (PARTITION BY T1.SKU,t1.shop) - SUM(t2.Replen) OVER (PARTITION BY T1.SKU,t1.shop)) > 0
                    , SUM(t2.Open_Stk) OVER (PARTITION BY T1.SKU,t1.shop) - SUM(t2.Replen) OVER (PARTITION BY T1.SKU,t1.shop)
                    , NULL) Calc
        FROM tbltest t1
        INNER JOIN tbltest t2 ON t1.sku = t2.sku AND t1.ShopPrioirty >= t2.ShopPrioirty
        ),
    cte2
    AS (
        SELECT *,
            min(Calc) OVER (PARTITION BY sku ORDER BY ShopPrioirty) min_calc,
            lag(total_stk - total_Replen) OVER (PARTITION BY sku ORDER BY ShopPrioirty) lag_calc
        FROM cte
        ),
    cte3
    AS (
        SELECT sku,shop,week,ShopPrioirty,Replen,Open_Stk,Open_Stk_Calc,
            iif(max(min_calc) >= max(isnull(lag_calc, 0)), max(min_calc), max(lag_calc)) new_Open_Stk_Calc
        FROM cte2
        GROUP BY sku,shop,week,ShopPrioirty,Replen,Open_Stk,Open_Stk_Calc
        )
    UPDATE t
    SET t.Open_Stk_Calc = c.new_Open_Stk_Calc
    FROM cte3 c
    INNER JOIN tblTest t ON c.sku = t.sku
        AND c.shop = t.shop;

    Output of table after update:


    Friday, June 14, 2019 6:52 PM
  • Hi Chairman Micharl,

    You also could try below query to see whether it work or not

    CREATE   TABLE [tblTEST] (
        SKU INT,
        Shop INT,
        Week INT,
        ShopPrioirty INT,
        Replen INT,
        Open_Stk INT,
        Open_Stk_Calc INT
        );
    INSERT INTO dbo.tblTEST
    VALUES
     (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);
    
    
      ;WITH CTE AS 
            (
            SELECT
            SKU,
            [Shop],
            [Week],
            ShopPrioirty,
            [Replen],      
            [Open_Stk],
           Open_Stk_Calc,
           isnull(Open_Stk,0)-lag(replen, 1,0) over (partition by sku order by sku,ShopPrioirty) as a
            FROM [tblTEST]),
    cte2  as 
    (select SKU,
            [Shop],
            [Week],
            ShopPrioirty,
            [Replen],      
            [Open_Stk],
           Open_Stk_Calc, sum(a) over (partition by sku order by sku,ShopPrioirty)  as t  from cte)
    
    update t
    SET t.Open_Stk_Calc = c.t
    FROM cte2 c
    INNER JOIN tblTest t ON c.SKU = t.SKU
        AND c.ShopPrioirty = t.ShopPrioirty;
    	

    Best Regards,
    Zoe Zhi


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, June 17, 2019 5:58 AM
  • Many thanks - this works.
    Wednesday, June 26, 2019 1:06 PM