1

Hope anyone who can shed some light.

I tried to bring the Week Ending Value to next week as Opening Stock value.

However, my update statement not working but returning me null. Eventually wrong Week Ending Value is computed due to Opening Stock value is not coming correctly.

No idea what went wrong ..

Appreciate your help.

enter image description here

This is the SQL I am having:

UPDATE s
SET WEEK_ENDING_CLOSING = ISNULL(OPENING_STOCK, 0) + CURR_WEEK_REMAINED,    
    OPENING_STOCK = (SELECT WEEK_ENDING_CLOSING 
                     FROM APP.ClosingSummary a
                     WHERE DATEADD(day, -7, s.WEEK_ENDING_DT) = a.WEEK_ENDING_DT 
                       AND s.OPERATOR = a.OPERATOR
                       AND s.PRODUCT_NAME = a.PRODUCT_NAME
                       AND s.PRODUCT_TYPE = a.PRODUCT_TYPE),
    PRE_WEDT = DATEADD(day, -7, s.WEEK_ENDING_DT)
FROM 
    APP.[ClosingSummary] s
1
  • 1
    I've updated your tags with sql-server but you should also update them to specify which version you're using too.
    – J.D.
    Jul 13, 2022 at 12:49

1 Answer 1

1

Looks like you can use an updatable CTE along with window functions

WITH cte AS (
    SELECT *,
      Last_WEEK_ENDING_CLOSING = LAG(s.WEEK_ENDING_CLOSING) OVER (PARTITION BY s.OPERATOR, s.PRODUCT_NAME, s.PRODUCT_TYPE ORDER BY s.WEEK_ENDING_DT),
      Last_WEEK_ENDING_DT =      LAG(s.WEEK_ENDING_DT     ) OVER (PARTITION BY s.OPERATOR, s.PRODUCT_NAME, s.PRODUCT_TYPE ORDER BY s.WEEK_ENDING_DT)
    FROM APP.ClosingSummary s
)
UPDATE cte
SET WEEK_ENDING_CLOSING = ISNULL(Last_WEEK_ENDING_CLOSING, 0) + CURR_WEEK_REMAINED,
    OPENING_STOCK = Last_WEEK_ENDING_CLOSING,
    PRE_WEDT = Last_WEEK_ENDING_DT
;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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