--出库表

CREATE TABLE WmsOutboundOrderLines ( [id] nvarchar(50), [orderNumber] nvarchar(50), [erpNumber] nvarchar(50), [itemCode] nvarchar(50), [packageType] nvarchar(50), [quantity] bigint, [itemStatus] nvarchar(50), [batchCode] nvarchar(50), [remarks] nvarchar(500), [createBy] nvarchar(50), [createTime] datetime, [updateBy] nvarchar(50), [updateTime] datetime )

INSERT INTO WmsOutboundOrderLines

VALUES

( N'FC16163C-397A-470E-AEF7-93D230BAA9A1', N'OGR1126001', N'EGR1126001', N'itemCode1', N'itemCodeName', 10, N'ZP', N'', N'', N'Test', N'2019-11-26T09:45:41.1', NULL, NULL ), 

( N'F1AC3E71-8971-4064-B35E-E3ACB91F1966', N'OGR1126001', N'EGR1126001', N'itemCode2', N'itemCodeName2', 10, N'ZP', N'B001', N'', N'Test', N'2019-11-26T09:46:00.08', NULL, NULL )



DROP TABLE WmsOutboundOrderLines

--库存表

CREATE TABLE WmsStorage ( [id] nvarchar(50), [orderNumber] nvarchar(50), [erpNumber] nvarchar(50), [itemCode] nvarchar(50), [packageType] nvarchar(50), [quantity] bigint, [zquantity] bigint, [itemStatus] nvarchar(50), [batchCode] nvarchar(50), [remarks] nvarchar(500), [createBy] nvarchar(50), [createTime] datetime, [updateBy] nvarchar(50), [updateTime] datetime )

INSERT INTO WmsStorage

VALUES

( N'7EE9C5F5-DC6A-40D1-8B08-750E9E58E92F', N'IGR1126001', N'EGR1126001', N'itemCode1', N'itemCodeName', 20, 0, N'ZP', N'B001', N'', N'Test', N'2019-11-26T09:46:41.967', NULL, NULL ), 

( N'59B2F30D-7FF0-4D9C-8A6A-C73DCEBD99AA', N'IGR1126001', N'EGR1126001', N'itemCode2', N'itemCodeName2', 20, 0, N'ZP', N'B001', N'', N'Test', N'2019-11-26T09:47:00.277', NULL, NULL ), 

( N'59B2F30D-7FF0-4D9C-8A6A-C73DCEBDA8A', N'IGR1126001', N'EGR1126001', N'itemCode2', N'itemCodeName2', 20, 0, N'ZP', N'', N'', N'Test', N'2019-11-26T09:47:00.277', NULL, NULL )



DROP TABLE WmsStorage



--如果出库表batchCode为空在匹配库存的时候就不用batchCode字段为判断条件,如果batchCode不为空则要给库存进匹配

--Bacth这个条件应该怎么写?,根据出库数量占用库存表的zquantity字段

--点用sql过程

;WITH a

AS (SELECT id,

           itemCode,

           itemStatus,

           batchCode,

           quantity,

           zquantity,

           [库存_SUM] =

           (

               SELECT SUM(quantity)

               FROM WmsStorage

               WHERE itemCode = a.itemCode

                     AND itemStatus = a.itemStatus

                     AND batchCode = a.batchCode

                     AND id <= a.id

           )

    FROM dbo.WmsStorage a

    WHERE quantity > 0),

      b

AS (SELECT id,

           itemCode,

           itemStatus,

           batchCode,

           SUM(quantity) AS OrderListQty,

           [出库_SUM] =

           (

               SELECT SUM(quantity)

               FROM WmsOutboundOrderLines

               WHERE itemCode = b.itemCode

                     AND itemStatus = b.itemStatus

                     AND batchCode = b.batchCode

                     AND id = b.id

           )

    FROM dbo.WmsOutboundOrderLines b

    WHERE b.orderNumber = 'OGR1126001'

    GROUP BY id,

             itemCode,

             itemStatus,

             batchCode)

UPDATE a

SET zquantity = CASE

                    WHEN b.[出库_SUM] < a.[库存_SUM] THEN

                        b.[出库_SUM]

                    ELSE

                        a.[库存_SUM]

                END - CASE

                          WHEN b.[出库_SUM] - b.OrderListQty < a.[库存_SUM] - a.quantity THEN

                              a.[库存_SUM] - a.quantity

                          ELSE

                              b.[出库_SUM] - b.OrderListQty

                      END

FROM a

    JOIN b

        ON a.itemCode = b.itemCode

           AND a.itemStatus = b.itemStatus

           AND a.batchCode = b.batchCode

           AND b.[出库_SUM] - b.OrderListQty < a.[库存_SUM]

           AND a.[库存_SUM] - a.quantity < b.[出库_SUM];

--执行后结果库存表



SELECT *FROM dbo.WmsStorage



/*

--占用结果不正确,这样写的话出库订单批次号为空,但库存批次号不为空,没有匹配上

id	orderNumber	erpNumber	itemCode	packageType	quantity	zquantity	itemStatus	batchCode	remarks	createBy	createTime	updateBy	updateTime

7EE9C5F5-DC6A-40D1-8B08-750E9E58E92F	IGR1126001	EGR1126001	itemCode1	itemCodeName	20	0	ZP	B001		Test	2019-11-26 09:46:41.967	NULL	NULL

59B2F30D-7FF0-4D9C-8A6A-C73DCEBD99AA	IGR1126001	EGR1126001	itemCode2	itemCodeName2	20	10	ZP	B001		Test	2019-11-26 09:47:00.277	NULL	NULL

59B2F30D-7FF0-4D9C-8A6A-C73DCEBDA8A	IGR1126001	EGR1126001	itemCode2	itemCodeName2	20	0	ZP			Test	2019-11-26 09:47:00.277	NULL	NULL

*/



--正确结果

/*

id	orderNumber	erpNumber	itemCode	packageType	quantity	zquantity	itemStatus	batchCode	remarks	createBy	createTime	updateBy	updateTime

7EE9C5F5-DC6A-40D1-8B08-750E9E58E92F	IGR1126001	EGR1126001	itemCode1	itemCodeName	20	10	ZP	B001		Test	2019-11-26 09:46:41.967	NULL	NULL

59B2F30D-7FF0-4D9C-8A6A-C73DCEBD99AA	IGR1126001	EGR1126001	itemCode2	itemCodeName2	20	10	ZP	B001		Test	2019-11-26 09:47:00.277	NULL	NULL

59B2F30D-7FF0-4D9C-8A6A-C73DCEBDA8A	IGR1126001	EGR1126001	itemCode2	itemCodeName2	20	0	ZP			Test	2019-11-26 09:47:00.277	NULL	NULL

*/