MS SQL Server cycle calculation

Question

Dears, I have an problems with a calculation of this formula: Example Description

Column "A" has always the same value for a group of SubIDs. In the "Result" column I need formula (function) like in the example columns "Result Formula Description". I tried many approaches but each of them help me with a half of the problem. Please, help me!

Answer 1

IMHO you should deal with recursion.

In this case I've used an UDF inline function to do the recursive job:

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
)

Then you can use a CROSS APPLY join with every distinct value of A column.

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

db<>fiddle here

Take care when using a recursive function, you should set OPTION (MAXRECURSION XXX) just to ensure it can hold all returned records.

Answer 2

with cte as (
 select 1 as subId, 100 as A, 0 as X, 10 as Y, 0 as Result union all
 select 2,100,10,0,0 union all
 select 3,100,20,0,0 union all
 select 4,100,0,50,0 union all
 select 5,100,60,0,0 union all
 select 1,1000,0,100,0 union all
 select 2,1000,100,0,0 union all
 select 3,1000,200,0,0 union all
 select 4,1000,0,500,0 
)
select 
 subid, 
 a, 
 x, 
 y, 
 a+x-y
 +ISNULL((select sum(x) from cte c2 where c2.a=c1.a and c2.subid<c1.subid),0)
 -ISNULL((select sum(y) from cte c2 where c2.a=c1.a and c2.subid<c1.subid),0)
 as Result
from cte c1
order by a,subid

output:

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