How to use WHILE LOOP to add value to list with condition, SQL Server 2008 -
i have loop query scenario below:
- orderqty = increase value 1 each loop
- runningstock = decrease value 1 each loop
- allocateqty = case when orderqty > 0 , runningstock > 0 1 else 0
- loop till runningstock=0 or total allocation=stockqty
query:
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 @order (priority,partcode,orderqty) values(1,'a',2), (2,'a',10), (3,'a',3), (4,'a',8); insert @stock(partcode,stockqty) values('a',20); declare @allocateqty int=1 declare @runningstock int=(select stockqty @stock) while @runningstock>=0 begin insert @result(priority,partcode,orderqty,runningstock,allocateqty) select priority, partcode, orderqty, @runningstock, case when @runningstock > 0 , orderqty > 0 1 else 0 end @order set @runningstock -=1 end select * @result go result:
priority partcode orderqty runningstock allocateqty 1 2 20 1 2 10 20 1 3 3 20 1 4 8 20 1 1 2 19 1 2 10 19 1 3 3 19 1 4 8 19 1 1 2 18 1 2 10 18 1 3 3 18 1 4 8 18 1 1 2 17 1 2 10 17 1 3 3 17 1 4 8 17 1 1 2 16 1 2 10 16 1 3 3 16 1 4 8 16 1 1 2 15 1 2 10 15 1 3 3 15 1 4 8 15 1 1 2 14 1 2 10 14 1 3 3 14 1 4 8 14 1 1 2 13 1 2 10 13 1 3 3 13 1 4 8 13 1 1 2 12 1 2 10 12 1 3 3 12 1 4 8 12 1 1 2 11 1 2 10 11 1 3 3 11 1 4 8 11 1 1 2 10 1 2 10 10 1 3 3 10 1 4 8 10 1 1 2 9 1 2 10 9 1 3 3 9 1 4 8 9 1 1 2 8 1 2 10 8 1 3 3 8 1 4 8 8 1 1 2 7 1 2 10 7 1 3 3 7 1 4 8 7 1 1 2 6 1 2 10 6 1 3 3 6 1 4 8 6 1 1 2 5 1 2 10 5 1 3 3 5 1 4 8 5 1 1 2 4 1 2 10 4 1 3 3 4 1 4 8 4 1 1 2 3 1 2 10 3 1 3 3 3 1 4 8 3 1 1 2 2 1 2 10 2 1 3 3 2 1 4 8 2 1 1 2 1 1 2 10 1 1 3 3 1 1 4 8 1 1 1 2 0 0 2 10 0 0 3 3 0 0 4 8 0 0 the correct 1 should this:
priority partcode orderqty runningstock allocateqty 1 2 20 1 2 10 19 1 3 3 18 1 4 8 17 1 1 1 16 1 2 9 15 1 3 2 14 1 4 7 13 1 1 0 12 0 2 8 12 1 3 1 11 1 4 6 10 1 1 0 9 0 2 7 9 1 3 0 8 0 4 5 8 1 1 0 7 0 2 6 7 1 3 0 6 0 4 4 6 1 1 0 5 0 2 5 5 1 3 0 4 0 4 3 4 1 1 0 3 0 2 4 3 1 3 0 2 0 4 2 2 1 1 0 1 0 2 3 1 1 3 0 0 0 any appreciate :)
you can loop through priorities , make sure orderqty updated accordingly. i'd this:
declare @allocatedqty int = 0 declare @allocateqty int = 1 declare @runningstock int = (select stockqty @stock) while @runningstock>=0 begin declare @priority int select top 1 @priority = priority @order order priority asc while @priority <= (select max(priority) @order) begin declare @orderqty int select @orderqty = orderqty - @allocatedqty @order priority = @priority select @allocateqty = case when @runningstock > 0 , @orderqty > 0 1 else 0 end insert @result(priority,partcode,orderqty,runningstock,allocateqty) select @priority, partcode, case when @orderqty >= 0 @orderqty else 0 end orderqty, @runningstock, @allocateqty @order priority = @priority set @priority += 1 set @runningstock = @runningstock - @allocateqty end set @allocatedqty += 1 if (@runningstock <= 0) break end select * @result go
Comments
Post a Comment