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)
--*/