CODEWITHSUNDEEP
sqlt-sql
tibz64
tibz64

Reputation: 45

Recursive CTE - Compute Parent Value based on child values

Here is an tree in which I want to compute total price at each level using a recursive T-SQL Query (presumably CTE) with the expected results below. At each level the total price equals to the sum of childs' values time a parent coefficient.

Parent1 (id:1 ; coef: 3)
|
|-SubParent2 (id:2 ; coef: 0.5)
|     |
|     |- Child (id:4) price=10
|     |- Child (id:5) price=15
|
|
|-SubParent3 (id:3; coef: 2)
|     |
|     |- Child (id:6) price=12
|     |- Child (id:7) price=13


DESIRED Results
---------------
 ID  | Totalprice   
 1   |    187.5     (Totalprice2[=12.5] + Totalprice3[=50]) * coef1[=3]
 2   |    12.5      (price4[=10]+price5[=15]) * coef2[= 0.5] 
 3   |    50        (price6[=12]+price7[=13]) * coef3[= 2]
 4   |    10
 5   |    15
 6   |    12
 7   |    13

I am sure I can do it using recursive CTE but I don't find how to do it since I cannot use group by in the recursive part of the CTE...

Creation Script

CREATE TABLE [dbo].[Instances] ( 
[ID] int NOT NULL, 
[Coef] float NULL,
[price] float NULL,
[ParentID] int NULL);

INSERT INTO Instances
Values
(1,3,NULL,NULL),
(2,0.5,NULL,1),
(3,2,NULL,1),
(4,1,10,2),
(5,1,15,2),
(6,1,12,3),
(7,1,13,3)

Thank you for your help

Upvotes: 2

Views: 568

Answers (1)

Ali Al-Mosawi
Ali Al-Mosawi

Reputation: 778

This was a bit tricky, anyhow common table expression do miracles. The idea I'm using is first to select all the leafs (records with prices only) then go step by step upwards each step I take the price multiplied by the coef, and then again till then end. after that, I will use a sum with a group by to get the final result,

My result match your expected output.

 ;with leafs as (
select *,ins.Coef*ins.price [total] from Instances ins where price is not null
union all
select ins.*,leafs.total*ins.Coef [total] from leafs
    inner join Instances ins on ins.ID=leafs.ParentID

)
select ID,sum(total)  Totalprice  from leafs 
group by ID
order by ID

The result of the query above is as below:-

ID  Totalprice
1   187.5
2   12.5
3   50
4   10
5   15
6   12
7   13

Hope this helps.

Upvotes: 1

Related Questions