### Question :

I have requiremtent to get the result as mentioned below in sql

Running Amt = Amt + Previous InterestAmt

How 202.5 comes in second row for RunningAmt

Previous row 100 value + current row 100 value + interestAmt, On same row after calculating this (Previous row 100 value + current row 100 value + interestAmt) calculation, need to get interestAmt for same.

I would like to understand how can we find out “RunningAmt” column from above image.

### Answer :

I had to used recursive calculation.

I added idx field , so that I can know the hierarchy of the records.

Also, added a field “clientID” , so to group them together.

```
;with cteSource As
(
SELECT 1 as ClientID, 1 as idx, 100 as Amt, 2.50 as InterestRate UNION ALL
SELECT 1 ,2, 100 , 2.50 UNION ALL
SELECT 1 ,3, 100 , 2.50 UNION ALL
SELECT 2 , 4 , 200 as Amt, 2.50 as InterestRate UNION ALL
SELECT 2 ,5, 200 , 2.50 UNION ALL
SELECT 2 ,6, 200 , 2.50
)
,cteSource_Rec AS
(
SELECT
idx,
ClientID,
Amt,
InterestRate,
CAST(Amt as DECIMAL(12,4)) as RunningAmt,
CAST(Amt * InterestRate/100.0 as DECIMAL(12,4)) as InterestAmt
FROM
(SELECT S.idx,S.ClientID ,S.Amt,S.InterestRate,
ROW_NUMBER() OVER(PARTITION BY S.clientID ORDER BY S.idx) as rn
FROM cteSource as S
)S
WHERE
s.RN = 1
UNION ALL
SELECT
S.idx,
S.ClientID,
S.Amt,
S.InterestRate,
CAST(L.RunningAmt + S.Amt + L.InterestAmt as DECIMAL(12,4)) as RunningAmt,
CAST((L.RunningAmt + S.Amt + L.InterestAmt)* L.InterestRate /100. as DECIMAL(12,4)) as InterestAmt
FROM
cteSource_Rec as L
INNER JOIN cteSource AS S
ON L.idx = S.idx - 1
AND L.clientID = S.ClientID
)
select * from cteSource_Rec ORDER BY ClientID, idx
```

Output:

```
idx ClientID Amt InterestRate RunningAmt InterestAmt
------ ----------- ----- --------------- ----------------------
1 1 100 2.50 100.0000 2.5000
2 1 100 2.50 202.5000 5.0625
3 1 100 2.50 307.5625 7.6891
4 2 200 2.50 200.0000 5.0000
5 2 200 2.50 405.0000 10.1250
6 2 200 2.50 615.1250 15.3781
```

I borrowed the setup from @Sabin Bio, for future posts, you will get much more response if post ddl and sample data as insert statements instead of images of data:

EDIT: added commented partition by if the example is to be extended

```
with t (amt, interestrate, inerestAmt) as (
SELECT 100, 2.50, 2.5
UNION ALL
SELECT 100, 2.50, 5.06
UNION ALL
SELECT 100, 2.50, 7.67
)
select t.*
-- cumulative sum of inerestamt up until previous row
-- map null to 0 for first row
, coalesce(sum(inerestamt) over (
-- partition by ...
order by inerestamt
rows between unbounded preceding
and 1 preceding
), 0)
-- cumulative sum of amt
+ sum(amt) over (
-- partition by ...
order by inerestamt
) as runningamt
from t;
```

this will required a recursive query

```
declare @sample table
(
Id int identity,
Amt decimal(10,2)
)
-- assuming the Interest Rate is constant. If not, just change to add to the table
declare @InterestRate decimal(5,2) = 2.5
-- sample data
insert into @sample (Amt) values (100), (100), (100);
-- the recursive query
with rcte as
(
select Id, Amt,
RunningAmt = convert(decimal(10,2), Amt),
InterestAmt = sum(Amt) over (order by Id) * @InterestRate / 100.0
from @sample
where Id = 1
union all
select s.Id, s.Amt,
RunningAmt = convert(decimal(10,2), s.Amt + r.RunningAmt + r.InterestAmt),
InterestAmt = (s.Amt + r.RunningAmt + r.InterestAmt) * @InterestRate / 100.0
from @sample s
inner join rcte r on r.Id = s.Id - 1
)
select *
from rcte
```