All Forums Database
deven_rk 4 posts Joined 09/16
07 Sep 2016
Compute with immediate previous row value derived

Hi All,


I have the below data, wherein I want to compute 'Amount' data dynamically in a 'Select' statement as given below, here I want

to refer the previous row value, apply an equation, compute a new value store it, for the next row again the same process of referring the

previous computed value. I shouldn't use stored procedures in this scenario, that's the catch. Please help, thank you.


Date, Amount

2016-11-01, 1000 

2016-11-02, 1100 (10% of 1000 + 1000) + previous value

2016-11-03, 1210 (10% of 1100 + 1100) + previous value

2016-11-04, 1331 (10% of 1210 + 1210) + previous value


abhishek.jadhav 28 posts Joined 02/08
07 Sep 2016

Let say your table has first record (2016-11-01, 1000) then you can run the below SQL once everyday which will calculate new Amount and insert it into the same table.


INS into dbname.Amt_tbl

Sel Date,(((Amount*0.1)+Amount)+Amount) from dbname.Amt_tbl where logdate In (Sel max(logdate) from dbname.Amt_tbl);

Abhishek Jadhav

dnoeth 4628 posts Joined 11/04
07 Sep 2016

Your calculation is similar to 
1000 * 1.1 ** (row_number() over (order by Date)-1)

To get he start value you might try

min(Amount) over () * 1.1 ** (row_number() over (order by Date)-1)


first_value(Amount) over (order by Date) * 1.1 ** (row_number() over (order by Date)-1)


You must sign in to leave a comment.