위와 같은 포맷의 데이터를 얻기 위해 쿼리문을 작성하였습니다.

전년대비 인상율이 구해지지 않아 마침표를 못 찍고 있습니다.

데이터의 정렬은 적용년월 기준으로 내림차순입니다.

전년대비 인상율을 구할때는 

(1-(세번째 Row 연봉 / 두번째 Row 연봉))*100 연봉

즉 (1-(33642000/36270000))*100 로 구해야 합니다.

아래 쿼리문에서 인상율 구하는게 쉽지가 않네요..

많은분들에 조언 부탁드립니다.

select
    a.etc_sno,   -- 사번
    (a.etc_year)gj_month,  --적용년월
    decode(a.gibon,0, (a.before_gibon*18),a.gibon)gibon, --기본급
    a.etc_adj, -- 조정수당
    a.etc_spr, -- 성과급
    a.etc_sap, -- 능력급
    a.meal,    -- 식대
    a.license, -- 면허수당
    decode(a.total,'',decode(a.gibon,0, (a.before_gibon*18),a.gibon)+a.etc_adj+a.etc_spr+a.etc_sap+a.meal+a.license,a.total)total, --연봉
    decode(c.overtime,'',0,c.overtime)overtime, --OT시간
    (b.ot)ot, -- OT수당
    (b.ib)ib, -- 인센티브
    (b.ex)ex, -- 기타수당
    (a.pr_gunro)all_total -- 총지급액
     from
     (
     select a.etc_gubun, a.etc_sno, a.etc_year, 
            decode(b.gibon,'',0,((b.gibon)*18))gibon, 
            decode(a.etc_adj,'',0,((a.etc_adj)*18))etc_adj, 
            decode(a.etc_spr,'',0,((a.etc_spr)*18))etc_spr, 
            decode(a.etc_sap,'',0,((a.etc_sap)*18))etc_sap, 
            decode(c.ps_amt,'',0,((c.ps_amt)*12))meal,
            decode(d.ps_amt,'',0,((d.ps_amt)*12))license,
            ((b.gibon)*18)+decode(a.etc_adj,'',0,((a.etc_adj)*18))+decode(a.etc_spr,'',0,((a.etc_spr)*18))+
            decode(a.etc_sap,'',0,((a.etc_sap)*18))+decode(c.ps_amt,'',0,((c.ps_amt)*12))+decode(d.ps_amt,'',0,((d.ps_amt)*12))total,
            (e.gibon)before_gibon, f.pr_gunro
     from
         (
             select etc_gubun, etc_sno, etc_year, etc_adj, etc_spr, etc_sap 
            from H_PR_YEAR_ETC_PAY 
             group by etc_gubun, etc_sno, etc_year, etc_adj, etc_spr, etc_sap
         )a,
         (
             select gj_gubun, gj_sno, gj_month, (gj_amt/12)gibon 
             from h_g_insa where gj_check=4
         )b,
         (
             select * from h_pr_pay where ps_code='J30'
         )c,
         (
             select * from h_pr_pay where ps_code='J55'
         )d,
         (
               select gj_gubun, gj_sno, (gj_amt/12)gibon 
             from h_g_insa 
               where gj_check=4 
               and gj_sno='20101001'
             and gj_month=(select max(gj_month)
                           from h_g_insa 
                           where gj_check=4
                           and gj_sno='20101001')
         )e,
         (
                select a.etc_gubun, a.etc_sno, a.etc_year, a.etc_year1, 
                decode(b.pr_gunro-decode(c.pr_pay,'',0,c.pr_pay),'',0,b.pr_gunro-decode(c.pr_pay,'',0,c.pr_pay))pr_gunro
                from 
                (
                    select etc_gubun, etc_sno, substr(etc_year,1,4)etc_year, (etc_year)etc_year1
                    from H_PR_YEAR_ETC_PAY
                    where etc_sno='20101001'
                )a,
                (
                    select pr_gubun, pr_sno, pr_year, pr_gunro 
                    from h_pr_younmal 
                    where pr_sno='20101001'
                )b,
                (
                    select pr_gubun, pr_sno, pr_year, 
                    sum(pr_pay1+pr_pay2+pr_pay3+pr_pay4+pr_pay5+pr_spay1+pr_spay2+pr_spay3+pr_spay4+pr_spay5)pr_pay
                    from h_pr_jongun 
                    where pr_sno='20101001'
                    group by pr_gubun, pr_sno, pr_year
                )c
                where a.etc_gubun = b.pr_gubun(+)
                and   a.etc_sno = b.pr_sno(+)
                and   a.etc_year = b.pr_year(+)
                and   a.etc_gubun = c.pr_gubun(+)
                and   a.etc_sno   = c.pr_sno(+)
                and   a.etc_year  = c.pr_year(+)
                )f
     where a.etc_gubun = b.gj_gubun(+)
     and   a.etc_sno = b.gj_sno(+)
     and   a.etc_year = b.gj_month(+)
     and   a.etc_gubun = c.ps_gubun(+)
     and   a.etc_sno = c.ps_sno(+)
     and   a.etc_year = c.ps_month(+)
     and   a.etc_gubun = d.ps_gubun(+)
     and   a.etc_sno = d.ps_sno(+)
     and   a.etc_year = d.ps_month(+)
     and   a.etc_gubun = e.gj_gubun(+)
     and   a.etc_sno = e.gj_sno(+)
     and   a.etc_sno = f.etc_sno(+)   
     and   a.etc_year = f.etc_year1(+)   
     and   a.etc_gubun = f.etc_gubun(+)   
     and   a.etc_sno ='20101001'
     )a,
     (
     select a.etc_gubun, a.etc_sno, substr(etc_year,1,4)etc_year, etc_year as gj_month_1, 
            decode(b.ot,'',0,(b.ot))ot,decode(b.ib,'',0,(b.ib))ib,decode(b.ex,'',0,(b.ex))ex
     from h_pr_year_etc_pay a,
     (        select  ps_gubun, ps_sno, substr(ps_month,1,4)ps_month,
                         sum(case when ps_code in ('J40','J41','J42','J43') then ps_amt else 0 end) as ot,
                         sum(case when ps_code in ('J53') then ps_amt else 0 end) as ib,
                         sum(case when ps_code in ('J20','J25','J44','J50','J51','J52','J54') then ps_amt else 0 end) as ex
             from h_pr_pay 
             group by ps_gubun, ps_sno, substr(ps_month,1,4)
      ) b
     where a.etc_gubun = b.ps_gubun(+)
     and   a.etc_sno   = b.ps_sno(+)
     and   substr(a.etc_year,1,4) = b.ps_month(+)
     and   a.etc_sno ='20101001'
     )b,
     (
     select pg_gubun, pg_sno, etc_year, (sum(pg_nitime)+sum(pg_extime)+sum(pg_sptime)+sum(pg_hotime)+sum(pg_gitime))overtime
     from
         (
             select a.pg_gubun, a.pg_sno, a.pg_month, c.months, a.pg_day, 
                    a.pg_nitime, a.pg_extime, a.pg_sptime, a.pg_hotime, a.pg_gitime, c.etc_year
             from
                 (
                     select pg_gubun, pg_sno, pg_month, pg_day, pg_nitime, pg_extime, pg_sptime, pg_hotime, pg_gitime
                     from h_pr_gunday
                 )a,
                 (
                     select pg_gubun, pg_sno, (pg_month || pg_day)days from h_pr_gunday
                 )b,
                 (
                     select etc_gubun, etc_sno, etc_year, substr(etc_year,1,4)months from h_pr_year_etc_pay
                 )c
             where a.pg_gubun = b.pg_gubun
             and   a.pg_sno   = b.pg_sno
             and   a.pg_month = substr(b.days,1,6)
             and   a.pg_day   = substr(b.days,7,8)
             and   a.pg_gubun = c.etc_gubun
             and   a.pg_sno   = c.etc_sno
             and   substr(a.pg_month,1,4) = c.months(+)
             and   b.days between  c.months-1||1221 and c.months||1220
         )
     where pg_sno='20101001'
     group by pg_gubun, pg_sno, etc_year
     )c
     where a.etc_gubun = b.etc_gubun(+)
     and   a.etc_sno = b.etc_sno(+)
     and   a.etc_year = b.gj_month_1(+)
     and   a.etc_sno  = c.pg_sno(+)
     and   a.etc_year = c.etc_year(+)
     and   a.etc_sno='20101001'
     order by a.etc_year asc