CODEWITHSUNDEEP
sqlsql-server
rody
rody

Reputation: 1

How to exponential increase every other years salary on a Column @SQL

Im trying to exponential increase every other years salary by %5.

for example: In my table i have 10 rows, which represents 10 years, there is a salary column in that table, i would like to write a code which will increase the salary every year by % 5 : so that it should look like this in every other row.

Year    Salary
2014    10000
2015    10500      
2016    11025

Anyone can help me with this please, I appreciate your time folks, have a good weekend.

Upvotes: 0

Views: 168

Answers (3)

etsa
etsa

Reputation: 5060

Similar to Radu, assuming an ID for several workers. The column SALARY is showed just for check 

CREATE TABLE SAL (ID INT, YEAR SMALLINT, SALARY NUMERIC(10,2))
 INSERT INTO SAL VALUES (1, 2014, 10000)
 INSERT INTO SAL VALUES (1, 2015, 10000)
 INSERT INTO SAL VALUES (1, 2016, 10000)
 INSERT INTO SAL VALUES (1, 2017, 10000)

 INSERT INTO SAL VALUES (2, 2014, 20000)
 INSERT INTO SAL VALUES (2, 2015, NULL)
 INSERT INTO SAL VALUES (2, 2016, NULL)
 INSERT INTO SAL VALUES (2, 2017, NULL)

WITH X1 AS 
  (
        SELECT  ID, YEAR, SALARY, CAST(SALARY*1.05 AS NUMERIC(10,2)) AS SAL_UPD     
        FROM SAL 
        WHERE YEAR=2014
        UNION ALL
        SELECT A.ID, A.YEAR, A.SALARY, CAST(X1.SAL_UPD*1.05 AS NUMERIC(10,2)) AS SAL_UPD  
        FROM SAL A
        INNER JOIN X1 ON A.YEAR = X1.YEAR+1 AND A.ID=X1.ID
        WHERE A.YEAR>2014
  )
  SELECT * FROM X1
  ORDER BY ID, YEAR

Output:

ID          YEAR   SALARY                                  SAL_UPD
----------- ------ --------------------------------------- ---------------------------------------
1           2014   10000.00                                10500.00
1           2015   10000.00                                11025.00
1           2016   10000.00                                11576.25
1           2017   10000.00                                12155.06
2           2014   20000.00                                21000.00
2           2015   NULL                                    22050.00
2           2016   NULL                                    23152.50
2           2017   NULL                                    24310.13

Deleting a year, the query for that ID stops:

DELETE FROM SAL WHERE YEAR=2016 AND ID = 2

ID          YEAR   SALARY                                  SAL_UPD
----------- ------ --------------------------------------- ---------------------------------------
1           2014   10000.00                                10500.00
1           2015   10000.00                                11025.00
1           2016   10000.00                                11576.25
1           2017   10000.00                                12155.06
2           2014   20000.00                                21000.00
2           2015   NULL                                    22050.00

Upvotes: 0

GSerg
GSerg

Reputation: 78183

What you have is essentially the compound interest. So use the formula to make it simple:

with years as (
  select year from (values (2014),(2015),(2016),(2017)) y(year)
),
starting_salary as (
  select cast(10000.0 as float) as salary
)
select
  year,
  starting_salary.salary * power(cast(1 + 0.05 as float), row_number() over(order by year) - 1)
from
  years
  cross join starting_salary
order by year;

Upvotes: 4

Radu Gheorghiu
Radu Gheorghiu

Reputation: 20499

You can use a recursive CTE to calculate the exponentially increased salaries for all the years in your table:

declare @salaries table (salaryYear int, salary int);
declare @minYear int, @maxYear int;

insert @salaries values (2006, 10000), (2007, NULL), (2008, NULL), (2009, NULL)
                    , (2010, NULL), (2011, NULL), (2012, NULL), (2013, NULL)
                    , (2014, NULL), (2015, NULL), (2016, NULL);

select @minYear = min(salaryYear) 
    , @maxYear = max(salaryYear)
from @salaries;

;with salaries as (
select salaryYear
    , salary
from @salaries
where salaryYear = @minYear

union all

select salaryYear + 1
    , salary * 105 / 100
from salaries
where salaryYear <= @maxYear
)
select *
from salaries

Upvotes: 0

Related Questions