you can use the following way to accomplish the given scenario.
this one is a reducing balance logic for each year. so if you give a range of the year to calculate the result you can use the following:
Declare @startYear int = 2000
, @endYear int = 2010
, @amt as int = 60000
, @RunningTotal as int = 0;
DECLARE @t TABLE
(
TID INT PRIMARY KEY,
pct int,
RunningTotal int
);
;With tally
as
(
select TOP 1000 ROW_NUMBER()Over(Order by (Select NULL)) -1 AS Rn
from sys.all_columns c
)
insert into @t
select @startYear + rn as years, case when rn = 0 then 0 else 10 end pct, 0.0
from tally
Where (@startYear + rn) <= @endYear
UPDATE @t
SET @RunningTotal = RunningTotal = (@amt - ((@amt * pct)/100))
, @amt = @RunningTotal
, pct = 10
FROM @t
option (maxdop 0);
select * from @t
For more understanding on Quirky check this
Link. Most of the people will say this is a undocumented way but i am using this approach from SQL SERVER 2000 till 2012 its and its working.