Advanced first Out Order allocation processing

Item Lot Number Inventory
AA P01 5
AA P02 10
BB p01 20

Order Item Order Number
1 AA 11
1 BB 10
2 AA 2
3 AA 1

To get:

Order item lot number out of library
1 AA P01 5
1 AA p02 6
1 BB p01 10
2 AA P02 2
3 AA P02 3

Requirements:
Use update, without functions, subqueries, cursors, loops, one sentence.

------------------------------------------------------------------------------------------------------

--a general idea-
-test data
CREATE TABLE TA (material varchar (10), Batch number varchar (10), inventory count int)
insert ta select ' AA ', ' P01 ', 5
UNION ALL SELECT ' AA ', ' P02 ', #
UNION ALL SELECT ' AA ', ' p03 ',
UNION ALL Select ' BB ', ' P01 ', 2
union ALL SELECT ' BB ', ' p02 ',
CREATE TABLE TB (order int, item varchar (10), order number int)
Insert TB Select 1, ' AA ', UNION ALL
Select 1, ' BB ',
union ALL Select 2, ' AA ', UNION ALL
Select 3, ' AA ', 1 go
--Generate temporary table
Select B. order, B. Material, A. Lot number, A. Inventory number, B. Order number, out of library =cast (0 as int) from
ta a,tb b
where a. Item =b. Item
ORDER by B. Material, A. Lot number, B. Orders go
-- Delete Test
drop table TA,TB
--implementation ideas

/*--generated temporary table the following

order  item  lot Number Inventory order number  out of the library    
------------------------------
1    aa    p01  5     One 5--and 6 unassigned, it's going to wait for the next batch
                                    --note The order number 1
2    aa    p01  5     0--no more processing because the unassigned number >0
3    aa    p01  5      1      0--no longer processed because unassigned number >0
1    aa    p02     11     6--with a new inventory of 10, compared with the last unassigned order number, >=
                                     --so the distribution, the distribution of the result is more than 4 of the inventory,
                                     --=0, note This order number +1, such as the allocation number >0, remember this order number
2    aa    p02     4--Last balance stock 4, distribution, 16 unassigned, note Order number 2
3    aa    p02     1      0--no longer processed, because the number of >0
1    aa    p03     0--There is a new inventory of 20, but order number < last recorded order number, do not handle
2    AA    p03  -     Meet the conditions, distribution, Yuku inventory 4
3    aa    p03     1      1-- Meet the conditions, allocate
1    bb    p01  2     2--Material change, restart distribution, unassigned number 8
                                     --Note The order number 1
1    bb    P02     8-There is a new inventory, allocated

(the number of rows affected is 11)
--*/

--Test data CREATE TABLE TA (item varchar (10), Batch varchar (10), Inventory number int)--/*--first set of test data insert Ta select ' AA ', ' P01 ', 5 union ALL Select ' AA ', ' P02 ', union ALL Select ' BB ', ' P01 ', 20--*//*--second set of test data insert Ta select ' AA ', ' P01 ', 5 union ALL SELECT ' AA ', ' p02 ',  UNION ALL SELECT ' AA ', ' P03 ', union ALL Select ' BB ', ' P01 ', 2 union ALL select ' BB ', ' p02 ', 20--*//*--third set of test data insert Ta Select ' AA ', ' P01 ', union ALL SELECT ' AA ', ' P02 ', # UNION ALL select ' BB ', ' P01 ', 20--*//*--fourth set of test data insert Ta select ' AA ', ' P01 ', union ALL SELECT ' AA ', ' P02 ', 8 union ALL SELECT ' AA ', ' P03 ', 6 union ALL SELECT ' AA ', ' p04 ', 3 UNION ALL select ' BB ' , ' P01 ', 2 union ALL select ' BB ', ' p02 ',--*/CREATE TABLE TB (order int, item varchar (10), order number int)--/*--first set of test data insert TB Selec T 1, ' AA ', union ALL select 1, ' BB ', # UNION ALL select 2, ' AA ', 2 union ALL select 3, ' AA ', 1--*//*--second set of test data insert TB SEL ECT 1, ' AA ', union ALL select 1, ' BB ', and union ALL select 2, ' AA ', union ALL select 3, ' AA ', 1--*//*--third set of test data insert TB Select 1, ' AA ', union aLL select 1, ' BB ', union ALL select 2, ' AA ', 2 union ALL select 3, ' AA ', 1--*//*--Fourth set of test data insert TB Select 1, ' AA ', 7 union All select 1, ' BB ', the union ALL select 2, ' AA ', 8 union ALL select 2, ' BB ', 8 union ALL select 3, ' AA ', 1--*/go

Select B. Orders, B. Material, A. Lot number, A. Inventory, B. Order number, out of library =cast (0 as int), unassigned =cast (null as int), inventory balance =cast (null as int), unassigned order =cast (null as int)	Into #t the from Ta a,tb b where a. Item =b. Item ORDER by B. Material, A. Lot number, B. Orders select * from #t-------------############## FIFO calculation Starts ##############----------------DECLARE @ Item varchar (10), @ Batch number varchar (10), @ order int DECLARE @ out of library int,@ inventory int,@ unassigned int up Date #t SET @ Inventory =case when item =@ item then case when Lot =@ batch number then @ Inventory Else @ Inventory + Inventory End E LSE inventory end, @ out of library =case when @ Inventory >0 then case when @ item = Item then case when IsNull (@ order, order) <= order T Hen case when @ unallocated >0 then case when @ Inventory <@ unassigned Then @ inventory Else @ Unassigned End E
				LSE Case @ Stock < Order number then @ inventory else order number end END ELSE 0 end else case when @ Inventory < order number then @ inventory else order number end END Else 0 end, @ no points With =case when @ out of the library >0 Then case when @ item = Item and @ Unassigned >0 then @ Unassigned-@ out of library else order number-@ out library end else @ unassigned End, @ order =case when @ item = Item then case when @ out of library >0 then case @ Unassigned >0 then order El 
					SE order +1 End ELSE @ order end Else Case @ Unassigned >0 then order else Order +1 end, @ Inventory =@ inventory-@ out, out of library =@, unassigned =@ unallocated, inventory balance =@ Inventory, no order =@ order, @ item = Item, @ Lot number = Lot-------------########### ### ends ##############---------------------############# #显示更新结果 ##############--Select order, material, lot number, out of library from #t WH Ere out of the library >0 order, items, lot number Go----------------##############----------------------select * from #t where out of the library >0 Orde R by order, item, lot number

/*--test Results

order          Item         batch number         out of library          
------------------------------------------ 
1           aa         p01        5
1           aa         p02        6
1           bb         p01
2           aa         p02        2
3           AA         p02        1

(the number of rows affected is 5)
--*/

/*---Some instructions:

The following in the process of the statement, just for debugging purposes, for easy observation, no actual processing use:
Unallocated =@ not assigned, inventory balance =@ inventory, no orders =@ orders,

Similarly, generating the following in a temporary table is only debugging (not required for actual processing):
, unassigned =cast (null as int), inventory balance =cast (null as int), unassigned order =cast (null as int)
--*/