求一个先进先出发货逻辑

[weisai]

物料表
物料 是否启动批号 是否可整包发料（库存足够时） 包装数量（是否可整包发料为1才有）
A 0 0 0
B 1 0 0
C 1 1 20
D 0 1 100
E 1 1 50

是否可整包发料（库存足够时） 的意思是， 比如C的包装数量是20 ，如果需要发料 30 ，库存只有35，那么就直接发30（不整包发）。
如果库存有100，就按整包发40（2*20），多发了10（40-30）



库存表
物料 批号 库存
A 20
B B001 20
B B002 10
B B003 50
C C001 20
C C002 25
C C003 200
D 220
E E001 5
E E002 10

需要发料表
物料 需求日期 需要发料数量
A 8-1 10
A 8-2 40
B 8-1 10
B 8-2 30
B 8-3 10
C 8-1 30
C 8-2 40
C 8-3 5
D 8-1 100
D 8-2 10
E 8-1 25
E 8-2 30
E 8-3 5


要求下面结果
1、发料结果（批号按批号的顺序，先进先出）
物料 需求日期 批号 发料数量
A 8-1 10
A 8-2 10
B 8-1 B001 10
B 8-2 B001 10
B 8-2 B002 10
B 8-2 B003 10
B 8-3 B003 10
C 8-1 C001 20
C 8-1 C002 10
C 8-2 C002 15
C 8-2 C003 25
C 8-3 C003 5
D 8-1 100
D 8-2 10
E 8-1 E001 5
E 8-1 E002 10

2 多发结果
物料 批号 发料数量
C C003 5
D 90

解析一下，这个多发按总数计算，比如C ，需求总数是75 ，包装数量是20 ，那么多发（库存足够的情况下）就是 20*4-75
D是 100*2-110=90


3 汇总结果
物料 需发 发料（总） 实发数量 多发
A 60 20 20 0
B 50 50 50 0
C 75 75 80 5
D 110 110 200 90
E 60 15 15 0

[Hello World,]

还剩下一个多发的记录，自己处理

DECLARE @Inv TABLE(code VARCHAR(10) NOT NULL,
                   bBatch BIT NOT NULL,
                   bPackageOut BIT NOT NULL,
                   PackageQty DECIMAL(18, 2) NOT NULL);

INSERT  @Inv(code, bBatch, bPackageOut, PackageQty)
VALUES('A', 0, 0, 0),('B', 1, 0, 0),('C', 1, 1, 20),('D', 0, 1, 100),('E', 1, 1, 50);

DECLARE @Stock TABLE(code VARCHAR(10) NOT NULL,batchNo VARCHAR(10) NOT NULL, stockQty DECIMAL(18,2) NOT NULL)
INSERT  @Stock(code, batchNo, stockQty)
VALUES('A', '', 20),('B', 'B001', 20), ('B', 'B002', 10),
      ('B', 'B003', 50),('C', 'C001', 20), ('C', 'C002', 25),
      ('C', 'C003', 200), ('D', '', 220), ('E', 'E001', 5), ('E', 'E002', 10);

DECLARE @Request TABLE(code VARCHAR(10) NOT NULL, Dt DATE NOT NULL, Qty DECIMAL(18, 2) NOT NULL);
INSERT  @Request(code, Dt, Qty)
VALUES('A', '2019-8-1', 10),('A', '2019-8-2', 40),('B', '2019-8-1', 10),('B', '2019-8-2', 30),
      ('B', '2019-8-3', 10),('C', '2019-8-1', 30),('C', '2019-8-2', 40),('C', '2019-8-3', 5),
      ('D', '2019-8-1', 100),('D', '2019-8-2', 10),('E', '2019-8-1', 25),('E', '2019-8-2', 30),('E', '2019-8-3', 5);

DECLARE @out TABLE(code VARCHAR(10) NOT NULL, DT DATE NOT NULL, BatchNo VARCHAR(10) NULL, outQty DECIMAL(18,2) NOT NULL)
DECLARE @stockSum TABLE(code VARCHAR(10) NOT NULL,Qty DECIMAL(18,2) NOT NULL)
INSERT INTO @stockSum(code, Qty)
SELECT code,SUM(stockQty) FROM @Stock GROUP BY code

DECLARE @code varchar(10), @DT DATE, @Qty DECIMAL(18,2), @remain DECIMAL(18,2)=0,@outQty DECIMAL(18,2)
DECLARE @stockCode VARCHAR(10), @stockBatchNo VARCHAR(10), @stockQty DECIMAL(18,2), @bPackageOut BIT, @PackageQty DECIMAL(18,2),@bBatch bit
DECLARE  cursor_Request CURSOR FORWARD_ONLY FAST_FORWARD  LOCAL FOR  --定义游标
    SELECT r.code, r.Dt, r.Qty,i.bPackageOut FROM @Request r INNER JOIN @Inv i ON i.code = r.code
    ORDER BY r.code,r.Dt
OPEN cursor_Request --打开游标
FETCH NEXT FROM cursor_Request INTO  @code,@DT,@Qty,@bPackageOut  --抓取下一行游标数据
WHILE @@FETCH_STATUS = 0
    BEGIN
        DECLARE cursor_stock CURSOR FORWARD_ONLY FAST_FORWARD LOCAL FOR
        SELECT      s.code, s.batchNo, s.stockQty, i.PackageQty, i.bBatch
        FROM        @Stock s
                    INNER JOIN @Inv i ON i.code = s.code
        WHERE       s.code = @code AND s.stockQty>0
        ORDER BY    s.batchNo;
		OPEN cursor_stock;
		FETCH NEXT FROM cursor_stock INTO @stockCode,@stockBatchNo,@stockQty,@PackageQty,@bBatch
		WHILE @@FETCH_STATUS=0
		BEGIN
			IF @Qty<=@stockQty
				BEGIN
					IF @bPackageOut=1 AND @bBatch=1 AND @stockQty%@PackageQty=0--整包出
						BEGIN
							SET @outQty=CEILING(@Qty/@PackageQty)*@PackageQty--计算出库数量
							INSERT INTO @out(code, DT, BatchNo, outQty)VALUES(@code,@DT,@stockBatchNo,@outQty)
							SET @remain=@stockQty-@outQty
						END
					ELSE
						BEGIN
							SET @remain=@stockQty - @Qty
							INSERT INTO @out(code, DT, BatchNo, outQty)VALUES(@code,@DT,@stockBatchNo,@Qty)
						END
					UPDATE @Stock SET stockQty=@remain WHERE code=@code AND batchNo=@stockBatchNo
					BREAK
				END
			ELSE
				BEGIN
					SET @Qty = @Qty-@stockQty
					INSERT INTO @out(code, DT, BatchNo, outQty)VALUES(@code,@DT,@stockBatchNo,@stockQty)
					SET @remain=0
					SET @bPackageOut=0
					UPDATE @Stock SET stockQty=@remain WHERE code=@code AND batchNo=@stockBatchNo
                END
			FETCH NEXT FROM cursor_stock INTO @stockCode,@stockBatchNo,@stockQty,@PackageQty,@bBatch
        END
		CLOSE cursor_stock
		DEALLOCATE cursor_stock
		FETCH NEXT FROM cursor_Request INTO @code, @DT, @Qty,@bPackageOut;
    END;

CLOSE cursor_Request; --关闭游标
DEALLOCATE cursor_Request; --释放游标

SELECT * FROM @out;
WITH need AS(
SELECT code,SUM(qty) NeedQty FROM @Request r GROUP BY code),
xx AS (
SELECT need.code, need.NeedQty, (SELECT SUM(outQty) FROM @out o WHERE o.code=need.code) OutQty,
(SELECT SUM(s.Qty) FROM @stockSum s WHERE s.code=need.code) StockQty,
(SELECT CASE WHEN i.bPackageOut=1 THEN CEILING(need.NeedQty/i.PackageQty)*i.PackageQty ELSE -1 END FROM @Inv i WHERE i.code=need.code) xxQty FROM need),
xxx AS (
SELECT xx.code, xx.NeedQty, xx.OutQty, CASE WHEN xx.xxQty=-1 THEN xx.OutQty ELSE CASE WHEN xx.xxQty>xx.StockQty THEN xx.StockQty ELSE xx.xxQty END END xxQty FROM xx)
SELECT *, CASE WHEN xxx.xxQty>xxx.NeedQty THEN xxx.xxQty-xxx.NeedQty ELSE 0 END overQty FROM xxx