Original post:
http://community.9cbs.net/expert/topic/3239/3239774.xml?temp=9.711856e-02
Material batch number inventory AA P01 5AA P02 10BB P01 20
Order material order 1 AA 111 BB 102 AA 23 AA 1
To get: Order Material Bottom Outbound 1 AA P01 51 AA P02 61 BB P01 102 AA P02 23 AA P02 3
Requirements: Using Update, don't have functions, subqueys, cursors, cycles, and one sentence.
-------------------------------------------------- -------------------------------------------------- -
- rough ideas
- Test data Create TABLE TA (Material varchar (10), batch number varchar (10), inventory INT) INSERT TA SELECT 'AA', 'P01', 5UNION All SELECT 'AA', 'P02', 10UNION All SELECT ' AA ',' P03 ', 20UNION All Select' BB ',' P01 ', 2UNION All Select' BB ',' P02 ', 20
Create Table TB (Order Int, Material Varchar (10), Order INT) Insert Tb Select 1, 'AA', 11UNION All Select 1, 'BB', 10UNION All Select 2, 'AA', 20UNION All SELECT 3, ' AA ', 1GO
- Generate a temporary table Select B. Order, b. Material, a. Batch number, a. Inventory number, b. Order number, out of the library = Cast (0 as int) from Ta A, TB bwhere a. Material = b. Material ORDER BY B. Material, a batch number, b. Order GO
- Delete Test DROP TABLE TA, TB - Implementation Ideas
/ * - The generated temporary table is as follows
Order material batch number inventory number order number outlet ---- -------- ------ ----------- 1 aa p01 5 11 5 - there are 6 Unsworthy, then wait for the next batch number to process - note the order number 12 AA P01 5 20 0 - no longer processed, because unallocated number> 03 AA P01 5 1 0 - no longer processed, because unallocated Number> 01 AA P02 10 11 6 - With new stock 10, compared with the last unallocated order number,> = - So allocation, the result of allocation is inventory 4, unallocated number - = 0, Make a note of this order number 1, such as allocation> 0, record this order number 2 AA P02 10 20 4 - last balanced inventory 4, allocation, Yu 16 unallocated, write down the order number 23 aa p02 10 1 0 - No longer processed, because the number of unsubstitution> 01 AA P03 20 11 0 - has a new inventory 20, but the order number of the order number, the order number, no processing 2 AA P03 20 20 16 - meet the conditions, Distribution, Yu Ling Stock 43 AA P03 20 1 1 - Message - There is new stock, allocation (the number of rows affected) - * /
-------------------------------------------------- -------------------------------------------------- ------------
- Test data Create TABLE TA (Material varchar (10), lot number var) - / * - First set test data Insert Ta Select 'AA', 'P01', 5UNION All Select ' AA ',' P02 ', 10UNION All SELECT' BB ',' P01 ', 20 - * /
/ * - The second set of test data Insert Ta select 'AA', 'P01', 5UNION All SELECT 'AA', 'P02', 10UNION All Select 'AA', 'P03', 20UNION All Select 'BB', ' P01 ', 2UNION All SELECT' BB ',' P02 ', 20 - * /
/ * - Third Set Test Data Insert Ta Select 'AA', 'P01', 50UNION All Select 'AA', 'P02', 10UNION All Select 'BB', 'P01', 20 - * /
/ * - The fourth set of test data Insert Ta select 'AA', 'P01', 50UNION All Select 'AA', 'P02', 8UNION All SELECT 'AA', 'P03', 6UNION All Select 'AA', ' P04 ', 3UNION All SELECT' BB ',' P01 ', 2UNION All Select' BB ',' P02 ', 20 - * / CREATE TABLE TB (Order INT, Material VARCHAR (10), Order INT) - / * - First set test data Insert TB SELECT 1, 'AA', 11UNION All Select 1, 'BB', 10UNION All Select 2, 'AA', 2UNION All SELECT 3, 'AA', 1 - * /
/ * - Second Test Data Insert TB SELECT 1, 'AA', 11UNION All Select 1, 'BB', 10UNION All Select 2, 'AA', 20UNION All SELECT 3, 'AA', 1 - * /
/ * - Third set of test data Insert TB SELECT 1, 'AA', 11UNION All SELECT 1, 'BB', 10UNION All SELECT 2, 'AA', 2UNION All SELECT 3, 'AA', 1- * /
/ * - Fourth set of test data Insert TB SELECT 1, 'AA', 7UNION All Select 1, 'BB', 10UNION All Select 2, 'AA', 8UNION All SELECT 2, 'BB', 8UNION All SELECT 3, 'aa', 1- * / go
Select B. Order, B. Material, a. Batch number, a. Inventory, b. order number, out of the ware = Cast (0 as int), unassigned = casters, inventory balance = Cast (Null As INT, unpaid order = cast (null as int) INTO #TFrom Ta A, TB Bwhere a. Material = b. Materials ORDER BY B. Material, a batch number, b. Order
- Generate the library data declare @ material varchar (10), @ 批 号 VARCHAR (10), @ order INTDECLARE @ @ @ @ inT, @ @ @ 无 分 分 分
inventory update #t set @ = @ = case when the material the material then case when Lot Lot then @ = @ inventory else @ inventory inventory inventory end else end, @ = case when the library @ stock> 0 then case when @ materials = material then case when isnull (@ orders, order) <= Order then case when @ unallocated> 0 then case when @ stocks <@ unassigned then @ inventory else @ not end else case when @ inventory allocation
- Delete Table TA, TBDROP TABLE #T
/*--Test Results
Order material lot number outlet ---------------------------- ----------- 1 aa p01 51 AA P02 61 BB P01 102 AA P02 23 AA P02 1
(The number of rows affects is 5 lines) - * /
/ * --- some instructions:
These, the following, only for debugging, it is convenient to observe, there is no actual processing: unallocated = @ unallocated, inventory balance = @ stock, unpaid order = @ orders,
Similarly, the following these are only debugged (actual processing is not required): No assignment = CAST (NULL AS INT), inventory balance = Cast (NULL AS INT), unpackled order = CAST NULL AS INT - * /
