I've been trying to figure out a way to calculate compound interest in SQL and I think that I can only do it using recursive SQL or actually return the rows using a cursor and calculate it in the calling program. Here is an example of my table:
Row_Nbr Principle Int_Rate
1 1000 .03
2 NULL .03
3 NULL .02
4 200 .02
The Principle column above would only contain an amount if additional principle was deposited or withdrawn. Note that the interest rate can change on each row. With the above table, I'd like to write SQL that gave the following results:
Row_Nbr Start_Bal Principle Interest End_Bal
1 0 1000.00 30.00 1030.00
2 1030.00 NULL 30.90 1060.90
3 1060.90 NULL 21.22 1082.12
4 1082.12 200.00 25.64 1037.76
Is there a way to do this without using recursive SQL? I'm not sure that it can be done in recursive SQL, but that would where I would go next.
The recursive SQL can handle this, an example is below. I wonder whether a more simple way exists.