Gross Requirements

Transact-SQL
1

I'm working with a Bill of Material table. Want to know if there is a way I could get a components Top Level parent.

Table Name: BOM

I'm using dashes as spaces to try and keep things lined up. The Code tag doesn't seem to work like it did on the old site.

Parent_Item --- Comp_item ------ Qty per
ABC--------------- 123 ------------------- 5
123 ----------------GKS------------------8
GKS ---------------GLS------------------2

what I'm looking to do is for all comp_items that start with GLS I want to find the top level parent. in the example above I would return ABC and it would be great if I could get the Qty needed of GLS which would be 582 = 40

2

You can select the text you want to tag as code and click the </> button on the menu bar of the edit window.

A recursive CTE something like shown below should get you what you are looking for. It is just something to get you started, and is not comprehensive. For example, if you had multiple rows with Comp_item = GLS, what should be the behavior? Most likely not what this code is doing.

DECLARE @item VARCHAR(32) = 'GLS';

;WITH cte AS
(
	SELECT Parent_Item, Qty, 1 AS Lvl 
	FROM BOM 
	WHERE Comp_item = @item
	
	UNION ALL
	
	SELECT b.Parent_Item, b.Qty*c.Qty, Lvl+1
	FROM
		BOM b
		INNER JOIN cte c ON
			c.Parent_item = b.Comp_item
)
SELECT TOP (1) * FROM cte ORDER BY Lvl DESC ;
3

Btw, 582 = 80 :wink: