How do I fix this logic?
I have a query with 3 variable tables: @result, @order and @stock. logic is the number of shares that should be allocated by loyalty (here i set = 1) to the whole order based on priority (FIFO). the number of shares must be allocated to zero, and allocateqty must be <= orderqty. the problem is in one of the orders, its allocateqty over orderqty (priority = 7), and the others are correct.
DECLARE @RESULT TABLE (priority int,partcode nvarchar(50),orderqty int, runningstock int, allocateqty int)
DECLARE @ORDER TABLE(priority int,partcode nvarchar(50),orderqty int)
DECLARE @STOCK TABLE(partcode nvarchar(50),stockqty int)
INSERT INTO @ORDER (priority,partcode,orderqty)
VALUES (1,'A',10),
(2,'A',50),
(3,'A',10),
(4,'A',40),
(5,'A',3),
(6,'A',5),
(7,'A',11),
(8,'A',10),
(9,'A',10),
(10,'A',10);
INSERT INTO @STOCK(partcode,stockqty)
VALUES('A',120)
IF (SELECT SUM(orderqty)FROM @ORDER)<(SELECT stockqty FROM @STOCK)
BEGIN
INSERT INTO @RESULT(priority,partcode,orderqty,allocateqty)
SELECT priority, partcode,orderqty,orderqty
FROM @ORDER
END
ELSE
BEGIN
DECLARE @allocatedqty int = 0
DECLARE @Lotsize int=1
DECLARE @allocateqty int = @Lotsize
DECLARE @runningstock int = (SELECT stockqty FROM @stock)
WHILE @runningstock>=0
BEGIN
DECLARE @priority int
SELECT TOP 1 @priority = priority FROM @order ORDER BY priority ASC
WHILE @priority <= (SELECT MAX(priority) FROM @order)
BEGIN
DECLARE @orderqty int
SELECT @orderqty = orderqty - @allocatedqty FROM @order WHERE priority = @priority
SELECT @allocateqty = CASE WHEN @runningstock > 0 AND @orderqty > 0 THEN @Lotsize ELSE 0 END
INSERT INTO @RESULT(priority,partcode,orderqty,runningstock,allocateqty)
SELECT @priority,
partcode,
CASE WHEN @orderqty >= 0 THEN @orderqty ELSE 0 END AS orderqty,
@runningstock,
@allocateqty
FROM @order
WHERE priority = @priority
SET @priority += 1
SET @runningstock = @runningstock - @allocateqty
END
SET @allocatedqty += @allocateqty
IF (@runningstock <= 0) BREAK
END
END
select * from @RESULT where priority=7;
SELECT priority,
sum(allocateqty) as allocated
from @RESULT
group by priority
result:
source to share
My reputation is below 50, so I can't add a comment.
you said your other order is correct, then priority = 7 is also correct. you can compare priorities 2 and 4 with 7. it's the same. i think your whole loop for orderqty
reaches only 10 times where priority 7 got 11, so it will leave 1.
All right or all wrong = x
EDIT:
Hi I found the answer.
Change
SET @allocatedqty += @allocateqty
to
SET @allocatedqty += 1
because when used, the SET @allocatedqty += @allocateqty
last order @allocateqty
is equal 0
, then it will always do @allocatedqty = 0
, then it will not increment.
Hope this really helps you.
EDIT based on @Jesuraja, the given answer should be:
SET @allocatedqty += @Lotsize
source to share
Since I'm not entirely sure what you are trying to achieve with records that will set your headroom to 0 or higher, I can just provide that. But this is much better than completing all your orders in a loop. Maybe you want to replace your loop.
DECLARE @RESULT TABLE (priority int,partcode nvarchar(50),orderqty int, runningstock int, allocateqty int)
DECLARE @ORDER TABLE(priority int,partcode nvarchar(50),orderqty int)
DECLARE @STOCK TABLE(partcode nvarchar(50),stockqty int)
INSERT INTO @ORDER (priority,partcode,orderqty)
--VALUES (1,'A',10),(2,'A',50),(3,'A',10),(4,'A',40),(5,'A',3),(6,'A',5),(7,'A',11),(8,'A',10),(9,'A',10),(10,'A',10); --your orders
VALUES (1,'A',1),(2,'A',2),(3,'A',3),(4,'A',4),(5,'A',5),(6,'A',6),(7,'A',7),(8,'A',8),(9,'A',9),(10,'A',10);
INSERT INTO @STOCK(partcode,stockqty)
--VALUES('A',50) -- your stock
VALUES('A',50)
IF (SELECT SUM(orderqty) FROM @ORDER)<(SELECT stockqty FROM @STOCK)
BEGIN
INSERT INTO @RESULT(priority,partcode,orderqty,allocateqty)
SELECT priority, partcode,orderqty,orderqty
FROM @ORDER
END
ELSE
BEGIN
;WITH dat AS(
SELECT s.partcode, s.stockqty, o.priority, o.orderqty,
ROW_NUMBER() OVER(PARTITION BY s.partcode ORDER BY o.priority DESC) as runningOrder
FROM @Stock as s
INNER JOIN @ORDER as o
ON s.partcode = o.partcode
)
INSERT INTO @RESULT(priority,partcode,orderqty,runningstock,allocateqty)
SELECT d1.priority, d1.partcode, d1.orderqty,
d1.stockqty - SUM(d2.orderqty) OVER(PARTITION BY d1.runningOrder) as runningstock,
CASE WHEN d1.stockqty - SUM(d2.orderqty) OVER(PARTITION BY d1.runningOrder) > 0 AND d1.orderqty > 0 THEN 1 ELSE 0 END
FROM dat as d1
INNER JOIN dat as d2
ON d1.partcode = d2.partcode
AND d1.runningOrder >= d2.runningOrder
END
select * from @RESULT where priority=7;
SELECT priority,
sum(allocateqty) as allocated
from @RESULT
group by priority
source to share