-2

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

enter image description here

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 at 12:08
  • 2
    Please consider reading this advice – mustaccio Nov 4 at 13:07
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
  • 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 at 11:28
1

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;
1

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 at 6:19
  • As OP commented on another answer, InterestAmt should be calculated from RunningAmt. – Squirrel Nov 5 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 at 8:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.