The problem of matching conditions and the look-CodePudding

 
- outbound table 
The 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), [few] nvarchar (500), [createBy] nvarchar (50), createTime datetime, [updateBy] nvarchar (50), [updateTime] datetime) 
INSERT INTO WmsOutboundOrderLines 
VALUES 
(N 'FC16163C - 397 - a - 470 - e - AEF7-93 d230baa9a1', N 'OGR1126001', N 'EGR1126001', N 'itemCode1', N 'itemCodeName' 10, N 'ZP', N ', N ', N 'Test', N '2019-11-26 T09: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-26 T09:46:00. 08', NULL, NULL) 

DROP TABLE WmsOutboundOrderLines 
- inventory list 
The 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), [few] nvarchar (500), [createBy] nvarchar (50), createTime datetime, [updateBy] nvarchar (50), [updateTime] datetime) 
INSERT INTO WmsStorage 
VALUES 
(N '7 ee9c5f5 - DC6A - 40-8 b08 d1-750 e9e58e92f', N 'IGR1126001', N 'EGR1126001', N 'itemCode1', N 'itemCodeName', 20, 0 N 'ZP', N 'B001', N ', N 'Test', N '2019-11-26 T09:46:41. 967', NULL, NULL), 
(N '59 b2f30d - 7 d9c ff0-4-8 a6a - C73DCEBD99AA', N 'IGR1126001', N 'EGR1126001', N 'itemCode2', N 'itemCodeName2', 20, 0 N 'ZP', N 'B001', N ', N 'Test', N '2019-11-26 T09:47:00. 277', NULL, NULL), 
(N '59 b2f30d - 7 d9c ff0-4-8 a6a - C73DCEBDA8A', N 'IGR1126001', N 'EGR1126001', N 'itemCode2', N 'itemCodeName2', 20, 0 N 'ZP', N ', N ', N 'Test', N '2019-11-26 T09:47:00. 277', NULL, NULL) 

DROP TABLE WmsStorage 

- if outbound table batchCode is empty when matching inventory no batchCode field conditions for judgment, if the batchCode don't empty the matching to inventory into 
- Bacth this condition should be how to write? , according to the delivery take inventory table zquantity field 
- point in SQL process 
; WITH a 
AS (SELECT id, 
ItemCode, 
ItemStatus, 
BatchCode, 
Quantity, 
Zquantity, 
[inventory _SUM]=
(
SELECT SUM (quantity) 
The FROM WmsStorage 
WHERE itemCode=Anderson temCode 
AND itemStatus=Anderson temStatus 
AND batchCode=a. atchCode 
AND id & lt; Anderson, d=
) 
The FROM dbo. WmsStorage a 
WHERE quantity & gt; 0), 
B 
AS (SELECT id, 
ItemCode, 
ItemStatus, 
BatchCode, 
The SUM (quantity) AS OrderListQty, 
[outbound _SUM]=
(
SELECT SUM (quantity) 
The FROM WmsOutboundOrderLines 
WHERE itemCode=b.i temCode 
AND itemStatus=b.i temStatus 
AND batchCode=b.b atchCode 
AND id=b.i d 
) 
The FROM dbo. WmsOutboundOrderLines b 
WHERE b.o rderNumber='OGR1126001' 
GROUP BY id, 
ItemCode, 
ItemStatus, 
BatchCode) 
UPDATE a 
The SET zquantity=CASE 
The WHEN b. [outbound _SUM] B. [outbound _SUM] 
The ELSE 
A. [inventory _SUM] 
END - CASE 
The WHEN b. [outbound _SUM] - b.O rderListQty & lt; A. [inventory _SUM] - a.q uantity THEN 
A. [inventory _SUM] - a.q uantity 
The ELSE 
B. [outbound _SUM] - b.O rderListQty 
END 
FROM a 
The JOIN b 
ON Anderson temCode=b.i temCode 
AND Anderson, temStatus=b.i temStatus 
AND a. atchCode=b.b atchCode 
AND b. [outbound _SUM] - b.O rderListQty & lt; A. [inventory _SUM] 
AND a. [inventory _SUM] - a.q uantity & lt; B. [outbound _SUM]; 
- the results after inventory list 

SELECT * FROM dbo. WmsStorage 

/* 
- take up the result is not correct, wrote of this outbound order batch number is empty, but stock batch number is not null, there is no matching 
Id orderNumber erpNumber itemCode packageType quantity zquantity itemStatus batchCode few createBy createTime updateBy updateTime 
7 ee9c5f5 - DC6A - 40-8 b08 d1-750 e9e58e92f IGR1126001 EGR1126001 itemCode1 itemCodeName 20 0 ZP B001 Test the 2019-11-26 09:46:41. 967 NULL NULL 
59 b2f30d - 7 d9c ff0-4-8 a6a - C73DCEBD99AA IGR1126001 EGR1126001 itemCode2 itemCodeName2 20 10 ZP B001 Test the 2019-11-26 09:47:00. 277 NULL NULL 
59 b2f30d - 7 d9c ff0-4-8 a6a - C73DCEBDA8A IGR1126001 EGR1126001 itemCode2 itemCodeName2 20 0 ZP Test the 2019-11-26 09:47:00. 277 NULL NULL 
*/

- results of a right 
/* 
Id orderNumber erpNumber itemCode packageType quantity zquantity itemStatus batchCode few createBy createTime updateBy updateTime 
7 ee9c5f5 - DC6A - 40-8 b08 d1-750 e9e58e92f IGR1126001 EGR1126001 itemCode1 itemCodeName 20 10 ZP B001 Test the 2019-11-26 09:46:41. 967 NULL NULL 
59 b2f30d - 7 d9c ff0-4-8 a6a - C73DCEBD99AA IGR1126001 EGR1126001 itemCode2 itemCodeName2 20 10 ZP B001 Test the 2019-11-26 09:47:00. 277 NULL NULL 
59 b2f30d - 7 d9c ff0-4-8 a6a - C73DCEBDA8A IGR1126001 EGR1126001 itemCode2 itemCodeName2 20 0 ZP Test the 2019-11-26 09:47:00. 277 NULL NULL 
*/
CodePudding user response:
If there is a batch number outbound order must according to the batch number and inventory table matching