# 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.

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

``````

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

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?
###### Who is Participating?

x
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.

MSSQL Senior EngineerCommented:
``````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
``````

Experts Exchange Solution brought to you by

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

Commented:
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
``````

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
)
``````

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
``````

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
``````

or "Now all parts on level 2":

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

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
Author Commented:
Thank you so much.  That's what I was looking for.
###### 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.