Calculate the price of a product from a multilevel BOM

Question

I'm not very advanced on sql, i read a lot about cte and recursive query to expand a bom but i still can't figure out how to solve my problem.

My table look like this:

DB_PARENT	DB_COMPONENT	DB_COEFF
S135427	M134842	0,05122125
S135695	M134905	0,2871687
F133818	S135482	0,5
S135424	S135423	1
S135424	M134534	1
F133783	S135454	1
F133924	S178585	1
S135602	S135599	1
S135087	M134609	0,068
F133353	M134684	0,085867225
F133513	S135287	1
S178407	M134591	2
S135438	M178128	9,663
S135619	S135622	1
S135619	S135628	1

I need to calculate the price of every product present in the column DB_parent.

Each product can be composed of either a raw material or another semi-finished product.

Some products on db_parent column have more than 1 row because they can be composed of more than 1 thing (raw material or semi-finisched).

I need to break down each product to reach the raw material and multiply it by the 'db-coeff' column.

So for example if a product is made from a raw material and a semi finisched, i have first to mutiply the raw materiale per the db-coeff, then look at how is made the semi finisched and calculate the semifinisched then add the two.

How can i do this? I am becoming crazy

thanks

I tried some recursive query but they don't work good This is the query i tried:

with a as(
  SELECT [dbo].[ANAG_DBASE].DB_PARENT, [dbo].[ANAG_DBASE].DB_COMPONENT, 1 * [dbo].[ANAG_DBASE].DB_COEFF as price, 1 as level,
  convert(varchar(max), [dbo].[ANAG_DBASE].DB_COMPONENT) as path
  FROM [dbo].[ANAG_DBASE]
 
  UNION ALL
  SELECT [dbo].[ANAG_DBASE].DB_PARENT, [dbo].[ANAG_DBASE].DB_COMPONENT, 1 * [dbo].[ANAG_DBASE].DB_COEFF as price, a.level + 1 as level,
  a.path + '/' + [dbo].[ANAG_DBASE].DB_COMPONENT
  FROM a
  INNER JOIN [dbo].[ANAG_DBASE] ON a.DB_PARENT = [dbo].[ANAG_DBASE].DB_COMPONENT)
select DB_PARENT, sum(price), level from a
group by DB_PARENT, level

Answer 1

I assume you are in manufacturing and you are referring to Bill Of Materials? If so, so am I, so this works out for us. If not, I apologize.

You are close. The CTE needs to first have an Anchor as the first query above your UNION. This Anchor should be a select to all parent assemblies that have a bill of material.

The second statement after the UNION should be the members that are within the hierarchy of the parent assemblies (Anchor statement)

After you UNION, I noticed you did:
INNER JOIN [dbo].[ANAG_DBASE] ON a.DB_PARENT = [dbo].[ANAG_DBASE].DB_COMPONENT)
However we are going from the parent to the child, so reverse it to:
INNER JOIN [dbo].[ANAG_DBASE] ON [dbo].[ANAG_DBASE].DB_PARENT = a.DB_COMPONENT)

DECLARE @ASSEMBLY nvarchar(30) = 'S135423';
;WITH CTE_BOM AS (
    SELECT 
        DB_PARENT, 
        DB_COMPONENT, 
        DB_COEFF, 
        1 AS [lvl], 
        CAST(DB_PARENT + '-->' + DB_COMPONENT as nvarchar(max)) as PATH
    FROM ANAG_DBASE 
    WHERE DB_PARENT = @ASSEMBLY --comment this row out if you want all of your assemblys' BOMs blown out
    UNION ALL
    SELECT 
        c.DB_PARENT, 
        c.DB_COMPONENT, 
        c.DB_COEFF,  
        [lvl] = b.lvl + 1, 
        PATH + '-->' + c.COMPONENT_ID
    FROM CTE_BOM b
    INNER JOIN ANAG_DBASE c ON b.DB_COMPONENT = c.DB_PARENT
)