Deducting the problem batch cycle-CodePudding

 

The create table # prescription list 
(
Prescription id int, 
Serial number int, 
Drug id int, 
The number of int 
) 
Insert into # prescription list 
Select 1,1,101,10 union all 
Select 1,2,102,20 union all 
Select 1,3,101,30 union all 
Select 1,4,103,40 


The create table # inventory list 
(
Receipt number int, 
Warehouse number int, 
Drug id int, 
The number of int 
) 
Insert into # inventory list 
Select 1001,1,101,20 union all 
Select 1001,2,102,10 union all 
Select 1001,3,103,50 union all 
Select 1002,1,101,8 union all 
Select 1002,2,102,30 union all 
Select 1003,1,101,20 

Get results - select * from # prescription list 
Number of prescription drug id id Numbers receipt, warehousing number deduct amount 
1 1 101 10 1001 1 
1 102 1001 2 10 
1 102 1002 2 10 
1, 3, 101 to 30, 1001 1 10 
1, 3, 101 to 30, 1002 1 8 
1, 3, 101 to 30, 1003 1 12 
1 April 103 40 1001 3 40

According to the prescription details inquiry to deduct from what inventory batch, the results of multiple serial number because of a lack of batch number, from multiple batches of deductions, allowed the same specifications of drugs in prescription, big trouble some advice, can need not cursor, check out the results directly

CodePudding user response:

Don't sink, a great god