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