percentage calculation from previous record

Question

I am trying to calculate percentage increase based on previous record. Say, I have record like this

----------------------------------------------------------------------------------
| user_id  | salary  | salary_date_change | percent_increase |  salary_record_id |
----------------------------------------------------------------------------------
| 10001    | 10000   |  01.10.2012        |                  |       1           |
| 10001    | 8000    |  01.09.2012        |                  |       2           |
| 10001    | 2000    |  01.07.2012        |                  |       3           |
| 10001    | 4000    |  01.08.2012        |                  |       4           |
| 10002    | 7500    |  01.09.2012        |                  |       5           |
| 10002    | 15000   |  01.10.2012        |                  |       6           |
---------------------------------------------------------------------------------

I'd like to run a update query or procedure and do the following:

----------------------------------------------------------------------------------
| user_id  | salary  | salary_date_change | percent_increase |  salary_record_id |
----------------------------------------------------------------------------------
| 10001    | 10000   |  01.10.2012        |   20             |       1           |
| 10001    | 8000    |  01.09.2012        |   50             |       2           |
| 10001    | 2000    |  01.07.2012        |   50             |       3           |
| 10001    | 4000    |  01.08.2012        |                  |       4           |
| 10002    | 7500    |  01.09.2012        |                  |       5           |
| 10002    | 15000   |  01.10.2012        |   50             |       6           |
---------------------------------------------------------------------------------

How can I do so?

This is what I tried:

 create or replace procedure salary_increase_migration (
     p_user_id integer,
     l_value varchar2 default null,
     l_old_salary number default null
 ) is 
 l_base_salary_prev_count integer default 0;
 CURSOR get_base_salary_prev IS
     SELECT * 
     FROM arr.user_multi_base_salary 
     ORDER BY salary_date_change DESC;  
 BEGIN                      

     FOR x IN get_base_salary_prev LOOP
         IF l_old_salary IS NOT NULL THEN
              l_value := ((x.salary/l_old_salary)-1)*100;
              UPDATE arr.user_multi_base_salary 
              SET percent_increase = l_value 
              WHERE user_id = x.user_id 
                  AND salary_record_id = x.salary_record_id;
              l_old_salary := x.salary;
         END IF;
     END LOOP;
 END;

Thanks in advance

Answer 1

to calculate the percent you may use:

select 
  user_id, salary, salary_change_date, 
  salary/(lag(salary) over (partition by user_id order by salary_change_date))*100 as percent_increase
from user_multi_base_salary;

to update the base_table:

merge into user_multi_base_salary t using(    
      select 
         user_id, salary, salary_change_date, 
         salary/(lag(salary) over (partition by user_id order by salary_change_date))*100 as percent_increase
      from user_multi_base_salary;
    )S
on (s.user_id = t.user_id and s.salary_change_date = t.salary_change_date)
when matched then update set
T.percent_increase = S.percent_increase

Answer 2

This ought to be a straightforward update. Given a record in your table, selecting the previous record ought to be very straightforward (especially if the required record is always one day prior).

Something like

 update my_table tgt
 set    percent_increase = (
          select (tgt.salary-src.salary)/case when src.salary = 0 then null else src.salary end
          from   my_table src
          where  src.user_id = tdt.user_id and 
                 src.salary_date_change = (
                   select max(src2.salary_date_change)
                   from   my_table src2
                   where  src2.user_id = src.user_id and
                          src2.salary_date_change < src.salary_date_change))