SQL Query - Recursive and Calculation

I have a recursive query to pull a multi-level BOM.  This BOM only consists purchased and fabricated parts.  If it's purchase part, then it's done and no need to do recursive.  If it's fabricated part, then I want I to recursive to find the job and purchase material to make this fabricated part.  I am able to get it to work, but I am having issue with my calculation.

If you look at this bill of material, when I input job# 12454, it query my database and find all the materials on this job.  The green are the materials on this job.  Part A, B, D, and E are all fabricated part, so I need to find the purchased material to make this fabricated part.  As you can also see from this, the orange is level 1 and blue is level 2 and so on and so on.  For this example, I am only going down to 2 levels deep.
Bill of Material
This is my query to get the above result.  This is working, but I cannot get my total to calculate correctly.
DECLARE @JobID		VARCHAR(20);

DECLARE @BOM TABLE
(
	JOB_ID			VARCHAR(20),
	PARENT_ID		VARCHAR(20),
	[TYPE]			VARCHAR(20),
	QTY				INT,
	COMPONENT_ID	VARCHAR(20),
	COMPONENT_TYPE	VARCHAR(20),
	COMPONENT_QTY	INT
);
	
INSERT INTO @BOM (JOB_ID, PARENT_ID, [TYPE], QTY, COMPONENT_ID, COMPONENT_TYPE, COMPONENT_QTY)
VALUES ('12454', 'A', 'Fab', 1, 'a1', 'Purc', 1),
	('12454', 'B', 'Fab', 1, 'c3', 'Fab', 1),
	('8822', 'c3', 'Fab', 1, 'a5', 'Purc', 2),
	('12454', 'D', 'Fab', 2, 'd2', 'Purc', 4),
	('12454', 'E', 'Fab', 1, 'aaa10', 'Fab', 2),
	('9999', 'aaa10', 'Fab', 2, 'bbb10', 'Purc', 2),
	('9999', 'aaa10', 'Fab', 2, 'ccc10', 'Fab', 2),
	('88271', 'ccc10', 'Fab', 2, 'ddd10', 'purc', 2);

SELECT @JobID = '12454';

WITH cteBOM AS
(
	SELECT BOM.JOB_ID, BOM.[TYPE], BOM.QTY, BOM.COMPONENT_ID, BOM.PARENT_ID,
		BOM.COMPONENT_TYPE, BOM.COMPONENT_QTY, BOM.COMPONENT_ID AS ROOTCOMPONENT_ID,
		0 AS [LEVEL]
	FROM @BOM AS BOM
	LEFT OUTER JOIN @BOM AS BOMParent
		ON BOM.PARENT_ID = BOMParent.COMPONENT_ID
	WHERE BOM.[TYPE] = 'FAB'
		AND BOM.JOB_ID = @JobID
		AND BOMParent.COMPONENT_ID IS NULL

	UNION ALL

	SELECT BOM.JOB_ID, BOM.[TYPE], BOM.QTY, BOM.COMPONENT_ID, BOM.PARENT_ID,
		BOM.COMPONENT_TYPE, BOM.COMPONENT_QTY, cteBOM.ROOTCOMPONENT_ID,
		cteBOM.[LEVEL] + 1 AS [LEVEL]
	FROM cteBOM
	INNER JOIN @BOM AS BOM
		ON cteBOM.COMPONENT_ID = BOM.PARENT_ID

)
SELECT  *
FROM	cteBOM
--WHERE COMPONENT_TYPE = 'PURC'
ORDER BY CASE	WHEN PARENT_ID = 'A' THEN 1
					WHEN PARENT_ID = 'a1' THEN 2
				WHEN PARENT_ID = 'B' THEN 5
					WHEN PARENT_ID = 'c3' THEN 6
						WHEN PARENT_ID = 'a5' THEN 7
				WHEN PARENT_ID = 'D' THEN 10
					WHEN PARENT_ID = 'd2' THEN 11
				WHEN PARENT_ID = 'E' THEN 15
					WHEN PARENT_ID = 'aaa10' THEN 16
						WHEN PARENT_ID = 'ddd10' then 17
						WHEN PARENT_ID = 'ccc10' then 18
			END
			
				

Open in new window


I only want to show the purchased materials.  So my result would be as followed:
Output
With the above:

1)  Item "a1" is a purchased part to make fabricated part A.  It consist of 1 to make item A so total is 1 usage for "a1".
2)  Item "a5" is purchased part to make fabricated part c3, which is then to make part B.  It require 2 of a5 to make 1 C3, and 1 of B.  Total usage is 2.
3)  Item "d2" is purchased part to make fabricated part D.  Total usage is 1.
4)  Item "ddd10"  is purchased part to make aaa10 and consist of 2 to make one aaa10.  Total usage is 4.
5)  Item "ccc10" is purchased part to make aaa10 and consist of 2 to make one aaa10.  Total usage is 4.

Not sure how to do this running value with the grouping of the grouping.  Do I use my rootcomponent_id to do the grouping followed by Parent_Id?
holemaniaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vitor MontalvãoMSSQL Senior EngineerCommented:
What about this?
WITH cteBOM AS
(
	SELECT BOM.JOB_ID, BOM.[TYPE], BOM.QTY, BOM.COMPONENT_ID, BOM.PARENT_ID,
		BOM.COMPONENT_TYPE, BOM.COMPONENT_QTY, BOM.COMPONENT_ID AS ROOTCOMPONENT_ID, 0 AS [LEVEL]
	FROM @BOM AS BOM
		LEFT OUTER JOIN @BOM AS BOMParent ON BOM.PARENT_ID = BOMParent.COMPONENT_ID
	WHERE BOM.[TYPE] = 'FAB'
		AND BOM.JOB_ID = @JobID
		AND BOMParent.COMPONENT_ID IS NULL
	UNION ALL
	SELECT BOM.JOB_ID, BOM.[TYPE], BOM.QTY, BOM.COMPONENT_ID, BOM.PARENT_ID,
		BOM.COMPONENT_TYPE, BOM.COMPONENT_QTY, cteBOM.ROOTCOMPONENT_ID, cteBOM.[LEVEL] + 1 AS [LEVEL]
	FROM cteBOM
		INNER JOIN @BOM AS BOM ON cteBOM.COMPONENT_ID = BOM.PARENT_ID
),
CTE_GroupBOM AS
(
	SELECT rootcomponent_id, SUM(component_qty) Extended_Total
	FROM cteBOM
	WHERE COMPONENT_TYPE = 'PURC'
	GROUP BY rootcomponent_id
)
SELECT cteBOM.COMPONENT_ID, cteBOM.COMPONENT_TYPE, CTE_GroupBOM.Extended_Total
FROM cteBOM
	INNER JOIN CTE_GroupBOM ON cteBOM.ROOTCOMPONENT_ID = CTE_GroupBOM.ROOTCOMPONENT_ID
WHERE COMPONENT_TYPE = 'PURC'
ORDER BY LEN(cteBOM.COMPONENT_ID), cteBOM.COMPONENT_ID

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
BitsqueezerCommented:
Hi,

I would simplify your base table and make it a simple hierarchy table like this:
CREATE TABLE dbo.tblBOM2(
	ID_BOM int IDENTITY(1,1) NOT NULL,
	JOB_ID int NOT NULL,
	COMPONENT_ID varchar(20) NOT NULL,
	PARENT_ID varchar(20) NULL,
	COMPONENT_TYPE varchar(4) NOT NULL,
	COMPONENT_QTY int NOT NULL,
	COMPONENT_ORDER int NOT NULL,
 CONSTRAINT PK_BOM2 PRIMARY KEY CLUSTERED 
(
	ID_BOM ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

INSERT INTO dbo.tblBOM2(ID_BOM, JOB_ID, COMPONENT_ID, PARENT_ID, COMPONENT_TYPE, COMPONENT_QTY, COMPONENT_ORDER)
SELECT 2, 12454, N'A', NULL, N'Fab', 1, 1 UNION ALL
SELECT 3, 12454, N'B', NULL, N'Fab', 1, 2 UNION ALL
SELECT 5, 12454, N'D', NULL, N'Fab', 1, 4 UNION ALL
SELECT 6, 12454, N'a1', N'A', N'Purc', 1, 1 UNION ALL
SELECT 7, 12454, N'c3', N'B', N'Fab', 1, 1 UNION ALL
SELECT 8, 8822, N'a5', N'c3', N'Purc', 2, 1 UNION ALL
SELECT 9, 12454, N'd2', N'D', N'Purc', 1, 1 UNION ALL
SELECT 10, 12454, N'aaa10', N'E', N'Fab', 2, 1 UNION ALL
SELECT 11, 9999, N'bbb10', N'aaa10', N'Purc', 2, 1 UNION ALL
SELECT 12, 9999, N'ccc10', N'aaa10', N'Fab', 2, 2 UNION ALL
SELECT 13, 88271, N'ddd10', N'ccc10', N'Purc', 2, 1 UNION ALL
SELECT 16, 12454, N'E', NULL, N'Fab', 1, 5

Open in new window


Now you have only one set of fields for each component independent of the level. The additional field "COMPONENT_ORDER" contains the sort order of all parts in their parent assembly to be able to sort the output easily.

Next is to build a general BOM function which lets you use it for any purpose, not only for this one:

CREATE FUNCTION dbo.fnBOM(@JobID AS int)
RETURNS TABLE
AS
RETURN
(

    WITH cteBOM AS
    (
	    SELECT BOM.JOB_ID, BOM.COMPONENT_TYPE, BOM.COMPONENT_QTY, BOM.COMPONENT_QTY AS QtyMultiply,
			 BOM.COMPONENT_ID,CAST(BOM.COMPONENT_ID AS nvarchar(200)) AS Display_Component_ID, BOM.PARENT_ID,
		    0 AS [LEVEL], COMPONENT_ORDER, RIGHT('000000000000' + CAST(BOM.COMPONENT_ORDER AS NVARCHAR(MAX)),12) AS A_Order,
		    CAST('' AS nvarchar(MAX)) AS ParentMPath,
		    '|' + CAST(BOM.ID_BOM AS nvarchar(MAX)) + '|' AS MPATH
	    FROM dbo.tblBOM2 AS BOM
	    WHERE BOM.JOB_ID = @JobID
	    AND	BOM.PARENT_ID IS NULL

	    UNION ALL

	    SELECT BOM.JOB_ID, BOM.COMPONENT_TYPE, BOM.COMPONENT_QTY, CAST(BOM.COMPONENT_QTY * cteBOM.COMPONENT_QTY AS int), BOM.COMPONENT_ID,
			 CAST(SPACE(([Level]+1)*4) + BOM.COMPONENT_ID AS nvarchar(200)), BOM.PARENT_ID,
		    cteBOM.[LEVEL] + 1 AS [LEVEL], 
		    BOM.COMPONENT_ORDER, 
		    cteBOM.A_Order + '|' + RIGHT('000000000000' + CAST(BOM.COMPONENT_ORDER AS NVARCHAR(MAX)),12),
		    cteBOM.MPATH AS ParentMPath,
		    cteBOM.MPATH + CAST(BOM.ID_BOM AS nvarchar(MAX)) + '|' AS MPATH
	    FROM cteBOM
	    INNER JOIN dbo.tblBOM2 AS BOM
	    ON cteBOM.COMPONENT_ID = BOM.PARENT_ID

    )
    SELECT cteBOM.JOB_ID, cteBOM.COMPONENT_TYPE, cteBOM.COMPONENT_QTY,
		 cteBOM.QtyMultiply, cteBOM.COMPONENT_ID, cteBOM.Display_Component_ID,
		 cteBOM.PARENT_ID, cteBOM.LEVEL, cteBOM.COMPONENT_ORDER, cteBOM.A_Order,
		 cteBOM.ParentMPath, cteBOM.MPATH
    FROM cteBOM
)

Open in new window


Now you can use that like you want, i.e. to produce your desired output:
DECLARE @JobID		int;
SELECT @JobID = 12454;

SELECT * FROM dbo.fnBOM(@JobID)
WHERE COMPONENT_TYPE = 'Purc'
ORDER BY A_Order

Open in new window


Or maybe next time all Fab by changing the COMPONENT TYPE in the WHERE. But you can also ask other questions like "I want to see all parts of component 'E'":

SELECT * FROM dbo.fnBOM(@JobID)
WHERE MPATH LIKE (SELECT MPATH FROM dbo.fnBOM(@JobID) WHERE COMPONENT_ID = 'E') + '%'
ORDER BY A_Order

Open in new window


or "Now all parts on level 2":

SELECT * FROM dbo.fnBOM(@JobID)
WHERE LEVEL=2
ORDER BY A_Order

Open in new window


And so on. On this way you can use the same CTE for multiple purposes without the need to create one every time again.

Instead building a materialized path you can (since SQL Server 2008) also use the hierarchy id datatype which can be indexed and has a better performance than building an own one at each function call like me here. I personally think that the hierarchy id is a little bit difficult to handle, but the result is the same.

Cheers,

Christian
0
holemaniaAuthor Commented:
Thank you so much.  That's what I was looking for.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.