accumulated calculation in sql server

Question

I want to do this query on sql server

I can do this with loop but would like to know if there is any easier way that has better performance

I have table for referrals , scenario 1 : if A has 5 points and refer B that has 7 then the query should show 12 pts for A (A points + B points) scenario 2 : if A has 5 and refer to B that has 7 and A refer to C that has 3 points and B refer to D that has 4 pts and so on.. in this case A takes all points of people A + B + C + D

my table look like this

Refs

sID bigint

sName varchar(50)

sPoints int

sRefID bigint

Answer 1

You can do this using recursive SQL. Try this:

With CTETable (sID, sRefID, sName, sPoints)
AS
(
SELECT Refs.sID, Refs.sRefID, Refs.sName, Refs.sPoints FROM Refs 
UNION ALL
SELECT Refs.sID, Refs.sRefID, CTETable.sName, Refs.sPoints 
FROM Refs INNER JOIN CTETable ON CTETable.sID = Refs.sRefID
)
Select sName, Sum(sPoints)
From CTETable
Group By CTETable.sName

This will yeild:

sName   TotalPoints
A   360
B   210
C   130
D   80
E   90
F   90
G   60
H   40
I   20
J   50