April 9, 2014 at 5:45 am
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.
April 10, 2014 at 9:19 am
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