Compute with immediate previous row value derived

Database

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

4 REPLIES
Junior Supporter

Re: Compute with immediate previous row value derived

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
Senior Apprentice

Re: Compute with immediate previous row value derived

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)

or 

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

Re: Compute with immediate previous row value derived

Hi dnothe,

 

If the percentage is not a constant, will be a join from a table dynamically using a date column, how do we do it then?
I believe we need to use LN() log function also for this, please share your comments with any possible new equation.

 

For example,

Date, Amount

2016-11-01, 1000 
2016-11-02, 1100 (10% of 1000 + 1000) + previous value
2016-11-03, 1232 (12% of 1100 + 1100) + previous value
2016-11-04, 1416.8 (15% of 1232 + 1232) + previous value

Senior Apprentice

Re: Compute with immediate previous row value derived

Correct, if it's a different constant for each row you need something like this:

SUM(Amount ) OVER (ORDER BY Date ROWS UNBOUNDED PRECEDING)
* EXP(SUM (LN (joined_percentage_column)) OVER (ORDER BY Date ROWS UNBOUNDED PRECEDING))