Build a table's parent-child relationship
There is a requirement that there is no relationship between the data in the table, just like a type, there are multiple warehouse picking times.
Code
-- Build a table's parent-child relationship
-- Hierarchical relationship between products
-- Build with row_number (). The top level is 0.
Insert Into Stock
( [ No ] -- No.
, [ Quantity ]
, [ ID ]
, [ Productsysno ]
, [ Intime ]
, [ Parentid ] )
Select No, ID, productsysno, ID - 1 Parentid From (
Select A. No, A. quantity, row_number () Over (Partition By A. productsysno Order By B. intime ASC ) ID,
Productsysno, intime From DBO. po_it Inner Join Po B On A. posysno = B. sysno
And B. Status = 4 ) T
Effect
Code
-- Recursively accumulate the number of warehouse receiving for the same product
With Product_cte1 (sysno, productsysno, ID, lastqty, quantity, Level )
As
(
Select [ Poitemsysno ] , Productsysno, ID, quantity, quantity, 1 Level From Item_stock Where Parentid = 0
Union All
Select E. [ Poitemsysno ] , E. productsysno, E. ID, D. quantity, E. Quantity + D. quantity, Level + 1
From DBO. item_stock As E, product_cte1 As D
Where E. parentid = D. id And E. productsysno = D. productsysno
)
--View data
Select * From
Product_cte1Option(Maxrecursion0)