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
Advertisement
Answer
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, p.Id AS base 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, c.base 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(partition by base order by level desc) + delta ELSE delta END sale FROM ( SELECT *, (power (0.8000, max(level) over(partition by base) - level) * 0.2) * price delta FROM cte_persons ) t ORDER BY id;