First-in-first-out order distribution processing

Original post address:

Http://community.csdn.net/Expert/topic/3239/3239774.xml? Temp = 9.711856e-02

Number of materials in stock
AA p01 5
AA p02 10
Bb p01 20

Order Quantity
1 aa 11
1 BB 10
2 AA 2
3 AA 1

To get:

Order material batch number warehouse picking
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, and loops.

Bytes ------------------------------------------------------------------------------------------------------

-- General idea

-- Test Data
Create Table TA (material varchar (10), batch number varchar (10), inventory count INT)
Insert ta select 'A', 'p01 ', 5
Union all select 'A', 'p02', 10
Union all select 'A', 'p03', 20
Union all select 'bb', 'p01 ', 2
Union all select 'bb', 'p02', 20

Create Table Tb (Order int, material varchar (10), order count INT)
Insert TB select 1, 'A', 11
Union all select 1, 'bb', 10
Union all select 2, 'A', 20
Union all select 3, 'A', 1
Go

-- Generate a temporary table
Select B. Order, B. Material, A. batch number, A. Inventory quantity, B. Order Quantity, warehouse picking = cast (0 as INT)
From ta a, TB B
Where 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 Quantity Order Quantity warehouse picking
------------------------------
1 aa p01 5 11 5 -- if there are 6 unallocated ones, wait for the next batch number to handle it.
-- Write down Order Number 1
2 AA p01 5 20 0 -- no longer processing, because the number of unallocated instances is greater than 0
3 AA p01 5 1 0 -- no longer processing, because the number of unallocated items> 0
1 aa p02 10 11 6 -- with new stock 10, compared with the order number not allocated last time,> =
-- Therefore, the allocation result is the inventory remaining 4 and the number of unallocated items.
-- = 0. Write down the order number + 1. For example, if the number of assignments is greater than 0, record the order number.
2 AA p02 10 20 4 -- last balance stock 4, allocation, remaining 16 not allocated, write down order number 2
3 AA p02 10 1 0 -- no more processing, because no score> 0
1 aa P03 20 11 0 -- there is a new stock of 20, but the order number <the order number recorded last time, not processed
2 AA P03 20 20 16 -- meet the conditions, allocate, and store stock 4
3 AA P03 20 1 1 -- meet conditions, assign
1 BB p01 2 10 2-material change, re-start allocation, number of unallocated 8
-- Write down Order Number 1
1 BB p02 20 10 8 -- new inventory, allocation

(11 rows are affected)
--*/

Bytes --------------------------------------------------------------------------------------------------------------

-- Test Data
Create Table TA (material varchar (10), batch number varchar (10), inventory count INT)
--/* -- First set of test data
Insert ta select 'A', 'p01 ', 5
Union all select 'A', 'p02', 10
Union all select 'bb', 'p01 ', 20
--*/

/* -- Second set of test data
Insert ta select 'A', 'p01 ', 5
Union all select 'A', 'p02', 10
Union all select 'A', 'p03', 20
Union all select 'bb', 'p01 ', 2
Union all select 'bb', 'p02', 20
--*/

/* -- The third set of test data
Insert ta select 'A', 'p01 ', 50
Union all select 'A', 'p02', 10
Union all select 'bb', 'p01 ', 20
--*/

/* -- Fourth set of test data
Insert ta select 'A', 'p01 ', 50
Union all select 'A', 'p02', 8
Union all select 'A', 'p03', 6
Union all select 'A', 'p04 ', 3
Union all select 'bb', 'p01 ', 2
Union all select 'bb', 'p02', 20
--*/

Create Table Tb (Order int, material varchar (10), order count INT)
--/* -- First set of test data
Insert TB select 1, 'A', 11
Union all select 1, 'bb', 10
Union all select 2, 'A', 2
Union all select 3, 'A', 1
--*/

/* -- Second set of test data
Insert TB select 1, 'A', 11
Union all select 1, 'bb', 10
Union all select 2, 'A', 20
Union all select 3, 'A', 1
--*/

/* -- The third set of test data
Insert TB select 1, 'A', 11
Union all select 1, 'bb', 10
Union all select 2, 'A', 2
Union all select 3, 'A', 1
--*/

/* -- Fourth set of test data
Insert TB select 1, 'A', 7
Union all select 1, 'bb', 10
Union all select 2, 'A', 8
Union all select 2, 'bb', 8
Union all select 3, 'A', 1
--*/
Go

Select B. Order, B. Material, A. batch number, A. Inventory quantity, B. Order Quantity, warehouse picking = cast (0 as INT)
, Unallocated = cast (null as INT), inventory balance = cast (null as INT), unconfigured order = cast (null as INT)
Into # T
From ta a, TB B
Where a. Material = B. Material
Order by B. Material, A. batch number, B. Order

-- Generate warehouse picking data
Declare @ material varchar (10), @ batch number varchar (10), @ order int
Declare @ outbound int, @ inventory int, @ unallocated int

Update # t set
@ Inventory = case when material = @ Material
Then case when batch number = @ batch number then @ inventory else @ inventory + inventory count end
Else inventory count end,
@ Warehouse picking = case when @ inventory> 0
Then case when @ material = Material
Then case when isnull (@ Order, order) <= order
Then case when @ unallocated> 0
Then case when @ Inventory <@ unallocated then @ inventory else @ unallocated end
Else case when @ Inventory <order quantity then @ inventory else order quantity end
Else 0 end
Else case when @ Inventory <order quantity then @ inventory else order quantity end
Else 0 end,
@ Unallocated = case when @ warehouse picking> 0
Then case when @ material = material and @ unallocated> 0
Then @ unallocated-@ warehouse picking
Else order quantity-@ warehouse end
Else @ unallocated end,
@ Order = case when @ material = Material
Then case when @ warehouse> 0
Then case when @ unallocated> 0 then order else order + 1 end
Else @ order end
Else case when @ unallocated> 0 then order else order + 1 end,
@ Inventory = @ inventory-@ warehouse picking,
Warehouse picking = @ warehouse picking,
 
Unallocated = @ unallocated, inventory balance = @ inventory, unassigned order = @ order,
@ Material = material, @ batch number = Batch Number

-- Display the update result
Select order, item, batch number, warehouse picking from # T
Where warehouse picking> 0
Order by order, material, batch number
Go

-- Delete test
Drop table Ta, TB
Drop table # T

/* -- Test Result

Order material batch number warehouse picking
------------------------------------------
1 aa p01 5
1 aa p02 6
1 BB p01 10
2 AA p02 2
3 AA p02 1

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

/* --- Some instructions:

The following statements are only used for convenience of observation during debugging and are not useful for actual processing:
Unallocated = @ unallocated, inventory balance = @ inventory, unassigned order = @ order,

Similarly, the following in the temporary table generation are only used for debugging (not required for actual processing ):
, Unallocated = cast (null as INT), inventory balance = cast (null as INT), unconfigured order = cast (null as INT)
--*/