SQL Warehouse库存领料消耗大于数量

Question

我的股票池被分成了几批。一个订单进来，我们从它指定的池中从第一批开始填写该订单。有时，收到的订单比池中的所有地块都大，我们需要到不同的池中获取库存。

我发现this very helpful link几乎完全描述了我想要的内容，但答案与我需要的数据显示的内容相差一步。

样本股票池、批次和数量：

+----+------+-----+----------+
| Id | Pool | Lot | Quantity |
+----+------+-----+----------+
|  1 |    1 |   1 |        5 |
|  2 |    1 |   2 |       10 |
|  3 |    1 |   3 |        4 |
|  4 |    2 |   1 |        7 |
|  5 |    3 |   1 |        1 |
|  6 |    3 |   2 |        5 |
+----+------+-----+----------+

订单示例：

+----+------+------------------+
| Id | Pool | QuantityConsumed |
+----+------+------------------+
|  1 |    1 |               17 |
|  2 |    2 |                8 |
|  3 |    3 |                6 |
+----+------+------------------+

从链接的问题中使用HABO的答案可以得到以下结果：

+------+-----+----------+------------------+-----------------+-----------------+------------------+
| Pool | Lot | Quantity | QuantityConsumed | RunningQuantity | RemainingDemand | SurplusOrDeficit |
+------+-----+----------+------------------+-----------------+-----------------+------------------+
|    1 |   1 |        5 |               17 |               0 |              12 | NULL             |
|    1 |   2 |       10 |               17 |               0 |               2 | NULL             |
|    1 |   3 |        4 |               17 |               2 |               0 | 2                |
|    2 |   1 |        7 |                8 |               0 |               1 | -1               |
|    3 |   1 |        1 |                6 |               0 |               5 | NULL             |
|    3 |   2 |        5 |                6 |               0 |               0 | 0                |
+------+-----+----------+------------------+-----------------+-----------------+------------------+

但是，我希望池2的订单然后转到前一个池，并在有剩余的情况下从它中提取。

最终想要的结果：

+------+-----+----------+------------------+-----------------+-----------------+------------------+
| Pool | Lot | Quantity | QuantityConsumed | RunningQuantity | RemainingDemand | SurplusOrDeficit |
+------+-----+----------+------------------+-----------------+-----------------+------------------+
|    1 |   1 |        5 |               17 |               0 |              12 | NULL             |
|    1 |   2 |       10 |               17 |               0 |               2 | NULL             |
|    1 |   3 |        4 |               17 |               1 |               0 | 1                |
|    2 |   1 |        7 |                8 |               0 |               0 | 0               |
|    3 |   1 |        1 |                6 |               0 |               5 | NULL             |
|    3 |   2 |        5 |                6 |               0 |               0 |  0               |
+------+-----+----------+------------------+-----------------+-----------------+------------------+

我在想，在链接的解决方案之前，有一对临时表strep，其中池订单数量与池的可用数量进行了检查，但随后我不知道如何拆分订单，以便从以前的池中获取所需的额外库存。

想法或建议将是有帮助的。我正在使用SQL Server2014，但也想在最近的DB2版本上执行此操作，因为我们正在过渡系统。

Answer 1

我决定直接使用您提供的连接表(称为wh)，出于显而易见的原因，我在其中添加了一个id列。

Rextester demo:

create table wh (Id int, Pool int, Lot int, Quantity int, QuantityConsumed int, RunningQuantity int, RemainingDemand int, SurplusOrDeficit int);
GO
insert into wh values (1,1,1,5,17,0,12,NULL);
insert into wh values (2,1,2,10,17,0,2,NULL);
insert into wh values (3,1,3,4,17,2,0,2);
insert into wh values (4,2,1,7,8,0,1,-1);
insert into wh values (5,3,1,1,6,0,7,NULL);
insert into wh values (6,3,2,5,6,0,2,0);

declare @continue int, @d_id int, @s_id int, @deficit int, @max_surplus int;
set @continue = 1;
while @continue = 1
begin
    -- Find the first pool with deficit:
    select @d_id = min(id) from wh where SurplusOrDeficit < 0;
    if @d_id is null
    begin
        -- We are done, all demand has been satisfied
        set @continue = 0;
    end
    else 
    begin
        select @deficit = -SurplusOrDeficit from wh where id = @d_id;
        -- Find the first pool (if such exists) to satisfy the remaining demand for id = @d_id:
        select @s_id = min(id) from wh where SurplusOrDeficit >= @deficit;
        if @s_id is null
        begin
            -- Partially satisfy the remaining demand from a pool which has the biggest surplus:
            select @max_surplus = max(SurplusOrDeficit) from wh where SurplusOrDeficit > 0;
            if @max_surplus is not null
            begin
                select @s_id = min(id) from wh where SurplusOrDeficit = @max_surplus;
                update wh set SurplusOrDeficit = 0, RunningQuantity = RunningQuantity - @max_surplus where id = @s_id;
                update wh set SurplusOrDeficit = SurplusOrDeficit + @max_surplus, RemainingDemand = RemainingDemand - @max_surplus where id = @d_id;
            end
            else
            begin
                -- We are done, some demand cannot be satisfied
                set @continue = 0;
            end
        end
        else
        begin
            update wh set RunningQuantity = RunningQuantity - @deficit, SurplusOrDeficit = SurplusOrDeficit - @deficit where id = @s_id;
            update wh set RemainingDemand = 0, SurplusOrDeficit = 0 where id = @d_id; 
        end
    end
end

select * from wh;