Recursive query compute parent values-CodePudding

I have 2 tables Persons and Sales. In Person there is relation between child and parent, I want to compute 20 percent of parent values with the following condition

Persons

Id | ParentId | Name
1    NULL       Tom
2    1          Jake
3    2          Kate
4    3          Neil

Sales

PersonId | Sale
4          500

I want to get result like this

Id | ParentId | Name | Sale
1    Null       Tom    100  <-- (500*20)/100 left 400
2    1          Jake   80   <-- (400*20)/100 left 320
3    2          Kate   64   <-- (320*20)/100 left 256
4    3          Neil   256  <-- (320*80)/100

I wrote this query but it does not give appropriate result

;WITH cte_persons
AS
(
    SELECT p.Id, p.ParentId, p.Name, s.Price FROM Persons AS p
        INNER JOIN Sales AS s ON s.PersonId = p.Id
    UNION ALL
    SELECT p.Id, p.ParentId, p.Name, CAST((c.Price - (c.Price*80)/100) AS DECIMAL(6, 2)) FROM @Persons AS p
        INNER JOIN cte_persons AS c ON c.ParentId = p.Id
)
SELECT * FROM cte_persons

CodePudding user response：

This should be a two steps algorithm. First traverse the hierachy to get max level. Then apply the level in a reverse order.

WITH cte_persons
AS
(
    SELECT 1 as level, p.Id, p.ParentId, p.Name, s.Price 
    FROM Persons AS p
    INNER JOIN Sales AS s ON s.PersonId = p.Id
    UNION ALL
    SELECT level   1, p.Id, p.ParentId, p.Name, c.Price 
    FROM Persons AS p
    INNER JOIN cte_persons AS c ON c.ParentId = p.Id
)
SELECT Id, ParentId, Name,
   CASE level WHEN 1 
   THEN price - sum(delta) over(order by level desc)   delta 
   ELSE delta END sale
FROM ( 
   SELECT *,
     (power (0.8000, max(level) over() - level) * 0.2) * price delta
   FROM cte_persons
) t
ORDER BY id;

db<>fiddle