SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table t (subID int, A int, X int, Y int);
insert into t values 
(1, 100,  0,   10),
(2, 100,  10,  0),
(3, 100,  20,  0),
(4, 100,  0,   50),
(5, 100,  60,  0),
(1, 1000, 0,   100),
(2, 1000, 100, 0),
(3, 1000, 200, 0),
(4, 1000, 0,   500);
9 rows affected

CREATE FUNCTION dbo.getResult(@A int)
RETURNS TABLE AS
RETURN
(
WITH tree AS
(
    SELECT   t1.subID, t1.A, t1.X, t1.Y, 
             Result = (A + X - Y)
    FROM     t t1
    WHERE    t1.A = @A AND t1.subID = 1
    
    UNION ALL
    
    SELECT  t2.subID, t2.A, t2.X, t2.Y, 
            Result = tree.Result + t2.X - t2.Y
    FROM    tree
    JOIN    t t2
            ON t2.A = tree.A and t2.subID = tree.subID + 1
)
SELECT   *
FROM     tree
)
SELECT DISTINCT
    r.subID, t.A, r.X, r.Y, r.Result
FROM
    t
CROSS APPLY 
    dbo.getResult(A) r
ORDER BY
    t.A, r.subID;


subID
A
X
Y
Result


1
100
0
10
90

2
100
10
0
100

3
100
20
0
120

4
100
0
50
70

5
100
60
0
130

1
1000
0
100
900

2
1000
100
0
1000

3
1000
200
0
1200

4
1000
0
500
700

subID	A	X	Y	Result
1	100	0	10	90
2	100	10	0	100
3	100	20	0	120
4	100	0	50	70
5	100	60	0	130
1	1000	0	100	900
2	1000	100	0	1000
3	1000	200	0	1200
4	1000	0	500	700