CODEWITHSUNDEEP
sqlpsqlpostgresql-9.5
Ajaz Rahman
Ajaz Rahman

Reputation: 1

Calculate Running Total Amount with Bonus

I have following table:

create table test_table
(
   employee_id integer, 
   salary_year integer, 
   raise_in_salary_perentage decimal(18,2), 
   annual_salary decimal(18,2)
);

**Test Data is following: **

insert into test_table values ( 1,2016, 0 , 100); 
insert into test_table values ( 1,2017, 10, 100); 
insert into test_table values ( 1,2018, 10, 100); 
insert into test_table values ( 1,2019, 0,  100); 
insert into test_table values ( 1,2020, 10,  100); 
insert into test_table values ( 2,2016, 10 , 100); 
insert into test_table values ( 2,2017, 10, 100); 
insert into test_table values ( 2,2018, 0, 100); 
insert into test_table values ( 2,2019, 0,  100); 
insert into test_table values ( 2,2020, 0,  100);

I am trying to achieve following output:

enter image description here

The cumulative salary should include the running total of annual salary over years for each employee. There is a percentage of raise every year, so if current year has a raise the cumulative salary will be sum of previous salaries plus the amount received in raise.

I tried to achieve it using following SQL, but results does seems right. Will be thankful for solution.

SELECT *
    ,sum(annual_salary) OVER (
        PARTITION BY employee_id ORDER BY salary_year ROWS BETWEEN UNBOUNDED PRECEDING
                AND CURRENT ROW
        ) AS cummulative_salary
    ,(
        sum(annual_salary) OVER (
            PARTITION BY employee_id ORDER BY salary_year ROWS BETWEEN UNBOUNDED PRECEDING
                    AND CURRENT ROW
            )
        ) + (
        sum(annual_salary) OVER (
            PARTITION BY employee_id ORDER BY salary_year ROWS BETWEEN UNBOUNDED PRECEDING
                    AND CURRENT ROW
            )
        ) * (
        sum(raise_in_salary_perentage) OVER (
            PARTITION BY employee_id ORDER BY salary_year ROWS BETWEEN UNBOUNDED PRECEDING
                    AND CURRENT ROW
            ) / 100
        ) AS csalary
FROM test_table;

Upvotes: 0

Views: 950

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269973

Based on your description, the increase in salary should be cumulative. However, a given year's increase should not affect previous years.

That is not what your desired results show. Based on my interpretation, I think you want:

with recursive cte as (
      select employee_id, salary_year, (t.annual_salary * (1 + raise_in_salary_perentage / 100.0))::numeric(18, 2) as annual_salary,
             raise_in_salary_perentage,
             (t.annual_salary * (1 + raise_in_salary_perentage / 100.0))::numeric(18, 2) as total
      from test_table t
      where salary_year = 2016
      union all
      select t.employee_id, t.salary_year, (cte.annual_salary * (1 + t.raise_in_salary_perentage / 100.0))::numeric(18, 2),
             t.raise_in_salary_perentage,
             (cte.total + cte.annual_salary * (1 + t.raise_in_salary_perentage / 100.0))::numeric(18, 2)
      from cte join
           test_table t
           on t.employee_id = cte.employee_id and t.salary_year = cte.salary_year + 1
     )
select *
from cte
order by employee_id, salary_year;

Here is a db<>fiddle.

Upvotes: 1

Related Questions