This seems like a piece of course work or a test thats been set internally by a senior.

However this code will work for any number of levels required though technically not elegant it does work

DECLARE @t TABLE

(node VARCHAR(10), parent VARCHAR(10), value INT, levelno smallint,Total int)

INSERT INTO @t

SELECT 'lv3a', 'lv2a', 15,NULL,NULL

UNION ALL SELECT 'lv3b', 'lv2a', 4,NULL,NULL

UNION ALL SELECT 'lv3c', 'lv2b', 0,NULL,NULL

UNION ALL SELECT 'lv3d', 'lv2c', 10,NULL,NULL

UNION ALL SELECT 'lv3e', 'lv2d', 2,NULL,NULL

UNION ALL SELECT 'lv3f', 'lv2d', 15,NULL,NULL

UNION ALL SELECT 'lv2a', 'lv1a', 3,NULL,NULL

UNION ALL SELECT 'lv2b', 'lv1a', 5,NULL,NULL

UNION ALL SELECT 'lv2c', 'lv1a', 7,NULL,NULL

UNION ALL SELECT 'lv2d', 'lv1b', 30,NULL,NULL

UNION ALL SELECT 'lv1a', 'root', 30,NULL,NULL

UNION ALL SELECT 'lv1b', 'root', 10,NULL,NULL

UNION ALL SELECT 'root', NULL, 100,NULL,NULL

Select * from @t

;WITH CTE_Level (node,parent,value, levelno)

AS

(

Select node,parent, value,0

From @t

Where parent is NULL

UNION ALL

Select a.node,a.parent, a.value,x.levelno+1

From @t a

JOIN CTE_Level x on x.node=a.parent

)

Update

x

Set

levelno=y.levelno

from @t x

JOIN CTE_Level y on y.node=x.node

Declare @max-2 int=(Select MAX(levelno) from @t)

Declare @count int

Update

@t

Set

Total=value

where

levelno=@max

Set @count=@max-1

While @count>-1

Begin

Update

x

Set Total=value-ABS(y.Total)

From @t x

JOIN

(Select a.node,SUM(ABS(b.Total)) Total

from @t a

JOIN @t b on b.parent=a.node

Group by a.node) y on x.node=y.node

where x.levelno=@count

set @count=@count-1

End

Select * from @t order by levelno

You might be able to get away with a second CTE rather than using the While loop, as on a large data set it wont perform.