SQL Adding Values based on the Hierarchy from Top to Bottom, with the bottom summing every from the top

The concept of what I am doing is summing the total waiting time from a higher level unto a lower level. And get the total waiting time for that particular entity. Below is a picture of the Levels of the Hierarchy:

My final output should be something like this.

The Total Waiting Time is explained by this instance. Total Waiting Times:

A = 1

B = 1 + 2 = 3

C = 1 + 3 = 4

D = 1 + 2 + 4 = 7

E = 1 + 2 + 5 = 8

F = 1 + 3 + 6 = 10

G = 1 + 3 + 7 = 10

I have an idea of joining the table with itself, but somehow it does not work, but somehow it doesn’t show the expected results. Is there a better way to do this? Thank you! 🙁

Answer

you can use recursive cte as shown below:

WITH cte AS (
    SELECT *, waitingTime AS TotalWaitingTime
    FROM waitingTime
    UNION ALL
    SELECT w.*, TotalWaitingTime + w.waitingTime
    FROM waitingTime w
    JOIN cte ON w.ParentObject = cte.Object
)
SELECT
    object,
    ParentObject,
    waitingTime,
    MAX(TotalWaitingTime) TotalWaitingTime
FROM  cte
GROUP BY  object,ParentObject,waitingTime
GO

object	ParentObject	waitingTime	TotalWaitingTime
A		1	1
B	A	2	3
C	A	3	4
D	B	4	7
E	B	5	8
F	C	6	10
G	C	7	11

db<>fiddle here