Updating a column by calculating based on previous row

Question

I have following three Tables. First of all i am using SQL Server 2005.

I want to clearly specify here the following 3 Tables

  1.Table Name:  PrimaryData   (Primary Key : SNo)

  SNO   Name, Designation BPS GrossPension

   1   ABCDEF  zzzzzz     21    25000
   2   WXYZA   YYYYYY     19    20000   

On basis of GrossPension , i have to calculate NetPension for each year


 **2. Table Name IncrementYear  (Primary Key : ID)**   
Following table shows the number of years with increase percentage value for each year, we have to calculate NetPension according to this table.
ID   IncYear Percentage

1   2010    15
2   2011    20
3   2012    15
4   2013    15
5   2014    15
6   2015    15
7   2016    15
8   2017    15
9   2018    15
10  2019    15
11  2020    12

Through a stored Procedure i insert the values to the following Table from above two tables i-e PrimaryData and IncrementYear

3. Table Name: PensionTable (Composite Primary Key : SNO ,Year)

Consisting of Columns : enter image description here

Formula for calculating NetPension is

  NetPension =   (GrossPension * 15 )/100

, where 15 is 2010 (Year) Percentage of Table Increment Year

Note: Here to mention only for year 2010 i have to calculate netPension 2010 from GrossPension (from PrimaryData).

After calculating NetPension for year 2010 , with NetPension 2010 i have to calculate succeeding year NetPension i-e 2011,2012 ....2020
for calculating year 2011 NetPension formula will be:(2010 NetPension * 20)/100+2010 NetPension , where 20 is 2011 Percentage of Table IncrementYear.
for calculating year 2012 NetPension formula will be :

(2011 NetPension * 15)/100 + 2011 NetPension

, where 15 is 2012 Percentage of Table IncrementYear.

and so on...

Here NetPension column is based on each Previous year NetPension , help me in such regards, if still not clear kindly send me your email i will send you complete Database files.

i-e 


  2010: (25000 * 0.15)+25000 =28750
    2011:  (28750* 0.2)+28750 = 34500
    2012:  (34500 * .15)+34500 = 39675

I think you need a recursive CTE. You can find a description here:msdn.microsoft.com/en-us/library/ms175972.aspx — PeterRing

Tobsey Tobsey · Accepted Answer · 2013-12-18T17:42:02

I'm pretty sure this query is correct. It recursively calculates the net pension for each record in the IncrementYear table. There may be some rounding issues so I suggest you fully verify the amounts:

;WITH CompoundRates
AS
(
    SELECT
        Sno,
        Bps,
        PrimaryData.GrossPension + (PrimaryData.GrossPension * IncrementYear.Percentage / 100) NetPension,
        IncrementYear.ID
    FROM
        PrimaryData 
        INNER JOIN IncrementYear ON IncrementYear.ID = 1
    WHERE
        ID = 1
    UNION ALL
    SELECT
        Compoundrates.Sno,
        Compoundrates.Bps,
        CompoundRates.NetPension + (CompoundRates.NetPension * IncrementYear.Percentage / 100),
        IncrementYear.ID
    FROM
        IncrementYear
        INNER JOIN CompoundRates ON CompoundRates.Id + 1 = IncrementYear.ID
)
SELECT * FROM CompoundRates ORDER BY Sno, ID

Updating a column by calculating based on previous row

2 Answers