SQL recursive assembly bom cost calculation

Question

Hello everyone,

I have attached table structure, where I want to roll up costs for an assembly starting at lower levels.
Attached image shows just one example where part P2 comes under part P1 and needs to be calculated first for cost and then rolled up to P1.
P2 also shows at Level 1 since it is prime assembly.
I strongly believe this requires a recursive query. Since I am new to recursive, I couldn't find the required output.
Request to please help in finding a solution. Any pointers/ideas will help.
Thanks in advance. Let me know if you need more details.

Answer 1

Check an adjusted query for new details:

declare @temp table ( parent varchar(max), part varchar(max), level int, cost money, qty int, top_nod varchar(max))

insert @temp values
('P1','P2',1,1,1,'P1'),
('P1','S1',1,1,6,'P1'),
('P1','P3',1,3,12,'P1'),
('P2','S2',2,1,5,'P1'),
('P2','S3',2,2,2,'P1'),
('P2','P4',2,3,4,'P1'),
('P4','S2',3,10,2,'P1'),
('P4','S3',3,20,3,'P1'),
('P4','P5',3,30,4,'P1'),
('A1','Q2',1,5,2,'A1'),
('A1','S1',1,20,4,'A1'),
('A1','S2',1,15,5,'A1'),
('Q2','R1',2,10,7,'A1'),
('Q2','S3',2,3,1,'A1'),
('R1','S4',3,4,2,'A1'),
('R1','S5',3,5,5,'A1'),
('R1','S6',3,7,2,'A1')

select * from @temp

;
with Q as
(
    select parent, part, level, cost, qty, top_nod
    from @temp
    union all
    select t.parent, Q.part, t.level, Q.cost, Q.qty * t.qty, Q.top_nod
    from @temp t
    inner join Q on Q.parent = t.part and Q.level = t.level + 1 and Q.top_nod = t.top_nod
)
select parent, sum(cost * qty) as final_cost
from Q 
where parent = top_nod
group by parent
order by parent

Results:

/*
parent   final_cost
A1         969,00
P1         864,00
*/

Answer 2

The design of that table is difficult to understand. It looks like there is quite a bit of redundant information. From what you say, it looks to as simple as

SELECT SUM(Cost) FROM tbl WERE Parent = 'P2' AND level = 2

But there might be something that I'm missing.

For the future: when you post sample data: do that as CREATE TABLE + INSERT statements. Then we can copy and paste into a query window. But we cannot copy from images.

Answer 3

Hi,@
Please check this :

;WITH CTE AS  
(  
 SELECT A.parent,A.part,A.level ,A.cost AS Acost,B.cost AS Bcost   
 FROM  #temp A    
 LEFT JOIN #temp B   
 ON B.parent = A.part AND B.level = A.LEVEL + 1  
)  
SELECT DISTINCT parent,part,level,  
                CASE WHEN Bcost IS NOT NULL   
	                 THEN SUM(Bcost)OVER(PARTITION BY parent,part)  
	 		         ELSE Acost END AS cost  
FROM CTE   
ORDER BY parent,part  

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 4

To obtain 809 for required part, check this example:

declare @temp table ( parent varchar(max), part varchar(max), level int, cost int, qty int)

insert @temp values
('P1', 'P2', 1,  1, 1),
('P1', 'S1', 1,  1, 6),
('P1', 'P3', 1,  3, 12),
('P2', 'S2', 2,  1, 5),
('P2', 'S3', 2,  2, 2),
('P2', 'P4', 2,  3, 4),
('P4', 'S2', 3, 10, 2),
('P4', 'S3', 3, 20, 3),
('P4', 'P4', 3, 30, 4)

select * from @temp

declare @parent varchar(max) = 'P1'
declare @part varchar(max) = 'P2'

;
with Q as
(
    select t1.parent, t1.part, t1.level, t1.cost * t1.qty as p
    from @temp t1
    left join @temp t2 on t2.parent = t1.part and t2.level = t1.level + 1
    where t2.part is null
    union all
    select t1.parent, t1.part, t1.level, /*(t1.cost * t1.qty) +*/ (p * t1.qty) as p
    from @temp t1
    inner join Q on Q.parent = t1.part and Q.level = t1.level + 1
)
select parent, part, sum(p) as p
from Q
where parent = @parent and part = @part
group by parent, part

To take into consideration all of the costs, obtaining 850, uncomment the expression.

Answer 5

@Viorel
I made small change to your first query. Finally it should be cost for each parent node.

declare @temp table ( parent varchar(max), part varchar(max), level int, cost int, qty int, parent_node varchar(max))

insert @temp values
('P1','P2',1,1,1,'P1'),
('P1','S1',1,1,6,'P1'),
('P1','P3',1,3,12,'P1'),
('P2','S2',2,1,5,'P1'),
('P2','S3',2,2,2,'P1'),
('P2','P4',2,3,4,'P1'),
('P4','S2',3,10,2,'P1'),
('P4','S3',3,20,3,'P1'),
('P4','P5',3,30,4,'P1'),
('A1','Q2',1,5,2,'A1'),
('A1','S1',1,20,4,'A1'),
('A1','S2',1,15,5,'A1'),
('Q2','R1',2,10,7,'A1'),
('Q2','S3',2,3,1,'A1'),
('R1','S4',3,4,2,'A1'),
('R1','S5',3,5,5,'A1'),
('R1','S6',3,7,2,'A1')

--select * from @temp

declare @parent varchar(max) = 'P1'
declare @part varchar(max) = 'P2'

;
with Q as
(
select
t1.parent,
t1.part,
t1.level,
t1.parent_node as parent_node,
t1.cost * t1.qty as p
from @temp t1
left join @temp t2
on t2.parent = t1.part
and t2.level = t1.level + 1
and t2.parent=t1.parent_node
where t2.part is null

 union all  

 select   
 t1.parent,   
 t1.part,   
 t1.level,  
 t1.parent_node,  
 (t1.cost * t1.qty) +(p * t1.qty) as p  
 from @temp t1  
 inner join Q   
 on Q.parent = t1.part   
 and Q.level = t1.level + 1  
 and Q.parent_node=t1.parent_node  

)

select
Q.parent_node,
sum(p)
from Q
where level=1
group by Q.parent_node