사번 |
적용년월 |
연봉계약서 |
전년대비
인상율 |
실제연봉(OT등 포함) |
기본급 |
조정 |
성과급 |
능력급 |
식대 |
면허 |
연봉 |
OT시간 |
OT수당 |
인센티브 |
기타수당 |
총지급액 |
20101001 |
201204 |
36,270,000 |
0 |
0 |
0 |
0 |
0 |
36,270,000 |
|
0 |
0 |
0 |
0 |
0 |
20101001 |
201104 |
36,270,000 |
0 |
0 |
0 |
0 |
0 |
36,270,000 |
|
0 |
0 |
0 |
0 |
37,949,000 |
20101001 |
201010 |
32,778,000 |
864,000 |
0 |
0 |
0 |
0 |
33,642,000 |
|
0 |
0 |
0 |
0 |
7,053,760 |
위와 같은 포맷의 데이터를 얻기 위해 쿼리문을 작성하였습니다.
전년대비 인상율이 구해지지 않아 마침표를 못 찍고 있습니다.
데이터의 정렬은 적용년월 기준으로 내림차순입니다.
전년대비 인상율을 구할때는
(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
|