Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

SQL to get % increase of amount from previous year

Ryansun-OracleNov 28 2012 — edited Nov 28 2012
Version 11g

Hi There,

I have a requirement, where I need to calculate the salary % increase and expense % increase from previous year,

So the output should be something like
Year        Salary       %increase                Rent         %Increase

2012       1000          1000%                   100                     1000%
2011       100             100%                    10                      100%(depending on last year value)
2010    . . . 
.
.
.
The insert scripts are 
create table sal
(exp_type   varchar2(100),
exp_date  date,
exp_amt number);


Insert into SAL (EXP_TYPE,EXP_DATE,"EXP_AMT") values ('Salary',to_date('01-JUN-11','DD-MON-RR'),685);
Insert into SAL (EXP_TYPE,EXP_DATE,"EXP_AMT") values ('Rent',to_date('01-JUN-11','DD-MON-RR'),84.89);
Insert into SAL (EXP_TYPE,EXP_DATE,"EXP_AMT") values ('Salary',to_date('01-JUL-11','DD-MON-RR'),685);
Insert into SAL (EXP_TYPE,EXP_DATE,"EXP_AMT") values ('Rent',to_date('01-JUL-11','DD-MON-RR'),84.89);
Insert into SAL (EXP_TYPE,EXP_DATE,"EXP_AMT") values ('Salary',to_date('01-AUG-11','DD-MON-RR'),687);
Insert into SAL (EXP_TYPE,EXP_DATE,"EXP_AMT") values ('Rent',to_date('01-AUG-11','DD-MON-RR'),89.04);
Insert into SAL (EXP_TYPE,EXP_DATE,"EXP_AMT") values ('Salary',to_date('01-SEP-11','DD-MON-RR'),687);
Insert into SAL (EXP_TYPE,EXP_DATE,"EXP_AMT") values ('Rent',to_date('01-SEP-11','DD-MON-RR'),89.04);
Insert into SAL (EXP_TYPE,EXP_DATE,"EXP_AMT") values ('Salary',to_date('01-OCT-11','DD-MON-RR'),750);
Insert into SAL (EXP_TYPE,EXP_DATE,"EXP_AMT") values ('Rent',to_date('01-OCT-11','DD-MON-RR'),89.04);
Insert into SAL (EXP_TYPE,EXP_DATE,"EXP_AMT") values ('Salary',to_date('01-NOV-11','DD-MON-RR'),750);
Insert into SAL (EXP_TYPE,EXP_DATE,"EXP_AMT") values ('Rent',to_date('01-NOV-11','DD-MON-RR'),89.04);
Insert into SAL (EXP_TYPE,EXP_DATE,"EXP_AMT") values ('Salary',to_date('01-DEC-11','DD-MON-RR'),755);
Insert into SAL (EXP_TYPE,EXP_DATE,"EXP_AMT") values ('Rent',to_date('01-DEC-11','DD-MON-RR'),89.04);
Insert into SAL (EXP_TYPE,EXP_DATE,"EXP_AMT") values ('Salary',to_date('01-JAN-12','DD-MON-RR'),755);
Insert into SAL (EXP_TYPE,EXP_DATE,"EXP_AMT") values ('Rent',to_date('01-JAN-12','DD-MON-RR'),89.04);
Insert into SAL (EXP_TYPE,EXP_DATE,"EXP_AMT") values ('Rent',to_date('01-FEB-12','DD-MON-RR'),89.04);
Insert into SAL (EXP_TYPE,EXP_DATE,"EXP_AMT") values ('Salary',to_date('01-FEB-12','DD-MON-RR'),750);
Insert into SAL (EXP_TYPE,EXP_DATE,"EXP_AMT") values ('Salary',to_date('01-MAR-12','DD-MON-RR'),766);
Insert into SAL (EXP_TYPE,EXP_DATE,"EXP_AMT") values ('Rent',to_date('01-MAR-12','DD-MON-RR'),89.04);
Insert into SAL (EXP_TYPE,EXP_DATE,"EXP_AMT") values ('Rent',to_date('01-JUN-12','DD-MON-RR'),89.04);
Insert into SAL (EXP_TYPE,EXP_DATE,"EXP_AMT") values ('Salary',to_date('01-JUN-12','DD-MON-RR'),740);
Insert into SAL (EXP_TYPE,EXP_DATE,"EXP_AMT") values ('Salary',to_date('02-JUL-12','DD-MON-RR'),735);
Insert into SAL (EXP_TYPE,EXP_DATE,"EXP_AMT") values ('Rent',to_date('02-JUL-12','DD-MON-RR'),89.04);
Insert into SAL (EXP_TYPE,EXP_DATE,"EXP_AMT") values ('Salary',to_date('01-AUG-12','DD-MON-RR'),739);
Insert into SAL (EXP_TYPE,EXP_DATE,"EXP_AMT") values ('Rent',to_date('01-AUG-12','DD-MON-RR'),93.4);
Insert into SAL (EXP_TYPE,EXP_DATE,"EXP_AMT") values ('Salary',to_date('02-SEP-12','DD-MON-RR'),740);
Insert into SAL (EXP_TYPE,EXP_DATE,"EXP_AMT") values ('Rent',to_date('02-SEP-12','DD-MON-RR'),93.4);
Insert into SAL (EXP_TYPE,EXP_DATE,"EXP_AMT") values ('Salary',to_date('01-APR-12','DD-MON-RR'),570);
Insert into SAL (EXP_TYPE,EXP_DATE,"EXP_AMT") values ('Rent',to_date('01-APR-12','DD-MON-RR'),89.04);
Insert into SAL (EXP_TYPE,EXP_DATE,"EXP_AMT") values ('Salary',to_date('01-MAY-12','DD-MON-RR'),740);
Insert into SAL (EXP_TYPE,EXP_DATE,"EXP_AMT") values ('Rent',to_date('01-MAY-12','DD-MON-RR'),89.04);
Insert into SAL (EXP_TYPE,EXP_DATE,"EXP_AMT") values ('Salary',to_date('01-NOV-12','DD-MON-RR'),845);
Insert into SAL (EXP_TYPE,EXP_DATE,"EXP_AMT") values ('Rent',to_date('01-NOV-12','DD-MON-RR'),93.4);
Insert into SAL (EXP_TYPE,EXP_DATE,"EXP_AMT") values ('Salary',to_date('01-OCT-12','DD-MON-RR'),900);
Insert into SAL (EXP_TYPE,EXP_DATE,"EXP_AMT") values ('Rent',to_date('01-OCT-12','DD-MON-RR'),93.4);
Insert into SAL (EXP_TYPE,EXP_DATE,"EXP_AMT") values ('Salary',to_date('01-FEB-10','DD-MON-RR'),508);
Insert into SAL (EXP_TYPE,EXP_DATE,"EXP_AMT") values ('Rent',to_date('01-FEB-10','DD-MON-RR'),84.89);
Insert into SAL (EXP_TYPE,EXP_DATE,"EXP_AMT") values ('Rent',to_date('03-MAR-10','DD-MON-RR'),84.89);
Insert into SAL (EXP_TYPE,EXP_DATE,"EXP_AMT") values ('Salary',to_date('03-MAR-10','DD-MON-RR'),500);
Insert into SAL (EXP_TYPE,EXP_DATE,"EXP_AMT") values ('Salary',to_date('01-APR-10','DD-MON-RR'),509);
Insert into SAL (EXP_TYPE,EXP_DATE,"EXP_AMT") values ('Rent',to_date('04-APR-10','DD-MON-RR'),84.89);
Insert into SAL (EXP_TYPE,EXP_DATE,"EXP_AMT") values ('Rent',to_date('01-MAY-10','DD-MON-RR'),84.89);
Insert into SAL (EXP_TYPE,EXP_DATE,"EXP_AMT") values ('Salary',to_date('01-MAY-10','DD-MON-RR'),514.46);
Any suggestions on how we can calculate the % increase at run time?


Thanks,
Ryan
This post has been answered by Frank Kulash on Nov 28 2012
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Dec 26 2012
Added on Nov 28 2012
8 comments
938 views