# Running Total by Computing column based on Previous value

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.

• How is the previous row defined? Is InerestAmt monotonic increasing, so that it can be used to order the rows? – Lennart Nov 4 '19 at 12:08

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
``````
• Thanks For sharing solution, I would like to calculate interestAmt based on current RuuningAmt column, so 202.5 so interest Amt is 5.06 if we multiple 202.5 * 0.025 = 5.06 and then use same one in next row calculation. – Tejas Nov 4 '19 at 11:28

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
``````
• Why will it require a recursive query? – Lennart Nov 5 '19 at 6:19
• As OP commented on another answer, `InterestAmt` should be calculated from `RunningAmt`. – Squirrel Nov 5 '19 at 6:33
• The OP:s question is very confusing, but if we add an ordering number (Id in your case), `100*(power(1+interestrate/100, Id)-1)` can be used to determine interestAmt. – Lennart Nov 5 '19 at 8:54