Running Total by Computing column based on Previous value

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 1

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

Answer 2

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;

Answer 3

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