CODEWITHSUNDEEP
sqlsql-servert-sql
Lawrence Wayne Ragudo
Lawrence Wayne Ragudo

Reputation: 43

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:

1

My final output should be something like this.

2

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! :(

Upvotes: 0

Views: 149

Answers (1)

eshirvana
eshirvana

Reputation: 24583

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

Upvotes: 5

Related Questions