update stock table

  • ordered quantity 7

    I have a table that im trying to write an update query that will work down the rows in OrderPriority and minus Ordered quantity until it reaches 0 then move to next row.

    SupplierId | OrderPriority | Stock

    1 | 1 |5

    2 | 2 |4

    3 | 3 |51

    Expected result table

    SupplierId | OrderPriority | Stock

    1 | 1 |0

    2 | 2 |2

    3 | 3 |51

    Any Ideas welcome.

  • try this...

    DECLARE @ORDER TABLE (SUPPLIERID INT,

    ORDERPRIORITY INT,

    STOCK INT)

    INSERT INTO @ORDER VALUES (1, 1, 5), (2, 2, 4), (3, 3, 51)

    SELECT * FROM @ORDER

    DECLARE @QUANTITY INT = 7

    DECLARE @Stock INT

    DECLARE @DEDUCTION INT

    DECLARE @REMAINING INT

    DECLARE @MAXSTOCK INT

    SET @MAXSTOCK = (SELECT SUM(STOCK)

    FROM @ORDER)

    IF @QUANTITY <= @MAXSTOCK

    BEGIN

    WHILE @QUANTITY > 0

    BEGIN

    SET @Stock = (SELECT STOCK

    FROM @ORDER

    WHERE ORDERPRIORITY = (SELECT MIN(ORDERPRIORITY)

    FROM @ORDER

    WHERE STOCK > 0))

    IF @Stock <= @QUANTITY

    BEGIN

    SET @DEDUCTION = @Stock

    --SELECT @DEDUCTION DEDUCTION

    SET @QUANTITY = @QUANTITY - @DEDUCTION

    UPDATE @ORDER

    SET STOCK = STOCK - @DEDUCTION

    WHERE STOCK > 0

    AND ORDERPRIORITY = (SELECT MIN(ORDERPRIORITY)

    FROM @ORDER

    WHERE STOCK > 0)

    END

    ELSE IF @Stock > @QUANTITY

    BEGIN

    SET @DEDUCTION = @QUANTITY

    --SELECT @DEDUCTION DEDUCTION

    UPDATE @ORDER

    SET STOCK = STOCK - @DEDUCTION

    WHERE STOCK > 0

    AND ORDERPRIORITY = (SELECT MIN(ORDERPRIORITY)

    FROM @ORDER

    WHERE STOCK > 0)

    SET @QUANTITY = 0

    END

    END

    END

    IF @QUANTITY > @MAXSTOCK

    BEGIN

    SELECT 'Insufficient stock'

    END

    SELECT * FROM @ORDER

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply