Advanced first Out Order allocation processing

Source: Internet
Author: User

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




Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.