# MS SQL Server cycle calculation

Posted on

### Question :

Dears, I have an problems with a calculation of this formula: 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.

``````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
``````

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.