2011-07-28 17:30:55
SQL 4 Oracle 오라클용 sql 실무
▒ 인사 근속기간 추출
최근 3년 퇴직자 중 총 근속기간이 1년 이상인 자
단, 중도에 입퇴사 반복된 자는 공백기간이 1개월 미만인 경우 계약기간이 연속된 것으로 계산하고,
공백기간이 1개월 이상인 경우 각각 1인으로 추출
----------------------------------------------------------------
select *
from
(
select 이름, 주민번호, CMY 입사일, RTR 퇴사일,
trunc(months_between( to_date( RTR,'yyyymmdd') , to_date(CMY,'yyyymmdd') ))+1 근속월
from (
select 주민번호,이름,min(입사일자) CMY,
(case when to_char(to_date(퇴사일자,'yyyymmdd')+30,'yyyymmdd')>= substr(NEXT_CMY1,1,8)
and to_char(to_date(substr(NEXT_CMY1,9,8),'yyyymmdd')+30,'yyyymmdd')>= substr(NEXT_CMY2,1,8)
and to_char(to_date(substr(NEXT_CMY2,9,8),'yyyymmdd')+30,'yyyymmdd')>= substr(NEXT_CMY3,1,8)
and to_char(to_date(substr(NEXT_CMY3,9,8),'yyyymmdd')+30,'yyyymmdd')>= substr(NEXT_CMY4,1,8)
and to_char(to_date(substr(NEXT_CMY4,9,8),'yyyymmdd')+30,'yyyymmdd')>= substr(NEXT_CMY5,1,8)
and to_char(to_date(substr(NEXT_CMY5,9,8),'yyyymmdd')+30,'yyyymmdd')>= substr(NEXT_CMY6,1,8)
then substr(NEXT_CMY6,9,8)
when to_char(to_date(퇴사일자,'yyyymmdd')+30,'yyyymmdd')>= substr(NEXT_CMY1,1,8)
and to_char(to_date(substr(NEXT_CMY1,9,8),'yyyymmdd')+30,'yyyymmdd')>= substr(NEXT_CMY2,1,8)
and to_char(to_date(substr(NEXT_CMY2,9,8),'yyyymmdd')+30,'yyyymmdd')>= substr(NEXT_CMY3,1,8)
and to_char(to_date(substr(NEXT_CMY3,9,8),'yyyymmdd')+30,'yyyymmdd')>= substr(NEXT_CMY4,1,8)
and to_char(to_date(substr(NEXT_CMY4,9,8),'yyyymmdd')+30,'yyyymmdd')>= substr(NEXT_CMY5,1,8)
then substr(NEXT_CMY5,9,8)
when to_char(to_date(퇴사일자,'yyyymmdd')+30,'yyyymmdd')>= substr(NEXT_CMY1,1,8)
and to_char(to_date(substr(NEXT_CMY1,9,8),'yyyymmdd')+30,'yyyymmdd')>= substr(NEXT_CMY2,1,8)
and to_char(to_date(substr(NEXT_CMY2,9,8),'yyyymmdd')+30,'yyyymmdd')>= substr(NEXT_CMY3,1,8)
and to_char(to_date(substr(NEXT_CMY3,9,8),'yyyymmdd')+30,'yyyymmdd')>= substr(NEXT_CMY4,1,8)
then substr(NEXT_CMY4,9,8)
when to_char(to_date(퇴사일자,'yyyymmdd')+30,'yyyymmdd')>= substr(NEXT_CMY1,1,8)
and to_char(to_date(substr(NEXT_CMY1,9,8),'yyyymmdd')+30,'yyyymmdd')>= substr(NEXT_CMY2,1,8)
and to_char(to_date(substr(NEXT_CMY2,9,8),'yyyymmdd')+30,'yyyymmdd')>= substr(NEXT_CMY3,1,8)
then substr(NEXT_CMY3,9,8)
when to_char(to_date(퇴사일자,'yyyymmdd')+30,'yyyymmdd')>= substr(NEXT_CMY1,1,8)
and to_char(to_date(substr(NEXT_CMY1,9,8),'yyyymmdd')+30,'yyyymmdd')>= substr(NEXT_CMY2,1,8)
then substr(NEXT_CMY2,9,8)
when to_char(to_date(퇴사일자,'yyyymmdd')+30,'yyyymmdd')>= substr(NEXT_CMY1,1,8)
then substr(NEXT_CMY1,9,8)
else 퇴사일자 end) rtr
from (
select RANK() OVER (PARTITION BY 주민번호 ORDER BY 입사일자 asc) 주민번호_sq,
주민번호, 이름, 입사일자, 퇴사일자,
trunc(months_between( to_date(퇴사일자,'yyyymmdd') , to_date(입사일자,'yyyymmdd') ))+1 근속월 ,
(select min(b.입사일자|| (case when b.퇴사일자>'20110631' then '20110701' else b.퇴사일자 end) )
from 인사원장_Table b
where b.주민번호=a.주민번호 and b.입사일자 >a.입사일자)
next_cmy1,
(select min(c.입사일자||(case when c.퇴사일자>'20110631' then '20110701' else c.퇴사일자 end) )
from 인사원장_Table c
where c.주민번호=a.주민번호
and c.입사일자 >
(select min(b.입사일자)
from 인사원장_Table b
where b.주민번호=a.주민번호 and b.입사일자 >a.입사일자)
)
next_cmy2,
(select min(d.입사일자||(case when d.퇴사일자>'20110631' then '20110701' else d.퇴사일자 end) )
from 인사원장_Table d
where d.주민번호=a.주민번호
and d.입사일자 >
(select min(c.입사일자)
from 인사원장_Table c
where c.주민번호=a.주민번호
and c.입사일자 >
(select min(b.입사일자)
from 인사원장_Table b
where b.주민번호=a.주민번호 and b.입사일자 >a.입사일자)
)
)
next_cmy3,
(select min(e.입사일자||(case when e.퇴사일자>'20110631' then '20110701' else e.퇴사일자 end) )
from 인사원장_Table e
where e.주민번호=a.주민번호
and e.입사일자 >
(select min(d.입사일자)
from 인사원장_Table d
where d.주민번호=a.주민번호
and d.입사일자 >
(select min(c.입사일자)
from 인사원장_Table c
where c.주민번호=a.주민번호
and c.입사일자 >
(select min(b.입사일자)
from 인사원장_Table b
where b.주민번호=a.주민번호 and b.입사일자 >a.입사일자)
)
)
)
next_cmy4,
(select min(f.입사일자||(case when f.퇴사일자>'20110631' then '20110701' else f.퇴사일자 end) )
from 인사원장_Table f
where f.주민번호=a.주민번호
and f.입사일자 >
(select min(e.입사일자)
from 인사원장_Table e
where e.주민번호=a.주민번호
and e.입사일자 >
(select min(d.입사일자)
from 인사원장_Table dc
where d.주민번호=a.주민번호
and d.입사일자 >
(select min(c.입사일자)
from 인사원장_Table c
where c.주민번호=a.주민번호
and c.입사일자 >
(select min(b.입사일자)
from 인사원장_Table b
where b.주민번호=a.주민번호 and b.입사일자 >a.입사일자)
)
)
)
)
next_cmy5,
(select min(g.입사일자||(case when g.퇴사일자>'20110631' then '20110701' else g.퇴사일자 end) )
from 인사원장_Table g
where g.주민번호=a.주민번호
and g.입사일자 >
(select min(f.입사일자)
from 인사원장_Table f
where f.주민번호=a.주민번호
and f.입사일자 >
(select min(e.입사일자)
from 인사원장_Table e
where e.주민번호=a.주민번호
and e.입사일자 >
(select min(d.입사일자)
from 인사원장_Table d
where d.주민번호=a.주민번호
and d.입사일자 >
(select min(c.입사일자)
from 인사원장_Table c
where c.주민번호=a.주민번호
and c.입사일자 >
(select min(b.입사일자)
from 인사원장_Table b
where b.주민번호=a.주민번호 and b.입사일자 >a.입사일자)
)
)
)
)
)
next_cmy6
from (
select 주민번호, 이름, 입사일자, 퇴사일자
from 인사원장_Table ba
where 퇴사일자>='20080630' and 퇴사일자<='20110631'
and 주민번호 in (select bs.주민번호
from 인사원장_Table bs
where bs.퇴사일자>='20080630' and bs.퇴사일자<='20110631' )
order by 주민번호,입사일자 asc
) a
) group by 주민번호,이름,
(case when to_char(to_date(퇴사일자,'yyyymmdd')+30,'yyyymmdd')>= substr(NEXT_CMY1,1,8)
and to_char(to_date(substr(NEXT_CMY1,9,8),'yyyymmdd')+30,'yyyymmdd')>= substr(NEXT_CMY2,1,8)
and to_char(to_date(substr(NEXT_CMY2,9,8),'yyyymmdd')+30,'yyyymmdd')>= substr(NEXT_CMY3,1,8)
and to_char(to_date(substr(NEXT_CMY3,9,8),'yyyymmdd')+30,'yyyymmdd')>= substr(NEXT_CMY4,1,8)
and to_char(to_date(substr(NEXT_CMY4,9,8),'yyyymmdd')+30,'yyyymmdd')>= substr(NEXT_CMY5,1,8)
and to_char(to_date(substr(NEXT_CMY5,9,8),'yyyymmdd')+30,'yyyymmdd')>= substr(NEXT_CMY6,1,8)
then substr(NEXT_CMY6,9,8)
when to_char(to_date(퇴사일자,'yyyymmdd')+30,'yyyymmdd')>= substr(NEXT_CMY1,1,8)
and to_char(to_date(substr(NEXT_CMY1,9,8),'yyyymmdd')+30,'yyyymmdd')>= substr(NEXT_CMY2,1,8)
and to_char(to_date(substr(NEXT_CMY2,9,8),'yyyymmdd')+30,'yyyymmdd')>= substr(NEXT_CMY3,1,8)
and to_char(to_date(substr(NEXT_CMY3,9,8),'yyyymmdd')+30,'yyyymmdd')>= substr(NEXT_CMY4,1,8)
and to_char(to_date(substr(NEXT_CMY4,9,8),'yyyymmdd')+30,'yyyymmdd')>= substr(NEXT_CMY5,1,8)
then substr(NEXT_CMY5,9,8)
when to_char(to_date(퇴사일자,'yyyymmdd')+30,'yyyymmdd')>= substr(NEXT_CMY1,1,8)
and to_char(to_date(substr(NEXT_CMY1,9,8),'yyyymmdd')+30,'yyyymmdd')>= substr(NEXT_CMY2,1,8)
and to_char(to_date(substr(NEXT_CMY2,9,8),'yyyymmdd')+30,'yyyymmdd')>= substr(NEXT_CMY3,1,8)
and to_char(to_date(substr(NEXT_CMY3,9,8),'yyyymmdd')+30,'yyyymmdd')>= substr(NEXT_CMY4,1,8)
then substr(NEXT_CMY4,9,8)
when to_char(to_date(퇴사일자,'yyyymmdd')+30,'yyyymmdd')>= substr(NEXT_CMY1,1,8)
and to_char(to_date(substr(NEXT_CMY1,9,8),'yyyymmdd')+30,'yyyymmdd')>= substr(NEXT_CMY2,1,8)
and to_char(to_date(substr(NEXT_CMY2,9,8),'yyyymmdd')+30,'yyyymmdd')>= substr(NEXT_CMY3,1,8)
then substr(NEXT_CMY3,9,8)
when to_char(to_date(퇴사일자,'yyyymmdd')+30,'yyyymmdd')>= substr(NEXT_CMY1,1,8)
and to_char(to_date(substr(NEXT_CMY1,9,8),'yyyymmdd')+30,'yyyymmdd')>= substr(NEXT_CMY2,1,8)
then substr(NEXT_CMY2,9,8)
when to_char(to_date(퇴사일자,'yyyymmdd')+30,'yyyymmdd')>= substr(NEXT_CMY1,1,8)
then substr(NEXT_CMY1,9,8)
else 퇴사일자 end)
) a
)
where 근속월>=12 and 퇴사일<>'20110701'
'SQL4Oracle' 카테고리의 다른 글
| PLSQL로 일괄처리 하기 (0) | 2023.03.03 |
|---|---|
| Oracle 테이블 정보 추출 (0) | 2023.03.03 |
| 그룹별 균등하게 금액 추출 (0) | 2023.03.03 |
| 그룹별 같은 비율로 추출 (0) | 2023.03.03 |
| Date type 연산 (0) | 2023.03.03 |