데이터베이스 질문과 답변
by 앗싸 [2017.03.08 10:28:58]
가족수당 을 산출 하려고 합니다.
조건은.
1. 조부, 부 만 60세 이상 2만원
2. 조모, 모 만 55세 이상 2만원
3. 배우자 4만원
4. 자녀(만 20세 미만)
- 1자녀 2만원, 2자년 6만원, 3자녀 이상 10만원,
예) 만20세 미만 자녀가 4명일 경우
- 성명1 : 20,000
- 성명 2 : 60,000
- 성명 3 : 100,000
- 성명 4 : 100,000
FAMILY 테이블 구성
(사원번호) (관계) (성명) (생년월일)
EmployeeNo Relation NameKor BirthDate
1111111111 부 가가가 1930/05/02
1111111111 모 나나나 1935/07/08
1111111111 배우자 다다다 1980/01/01
1111111111 자녀 자자자 1995/05/05
1111111111 자녀 라라라 2010/03/03
1111111111 자녀 마마마 2011/05/05
1111111111 자녀 바바바 2013/07/10
1111111111 자녀 사사사 2015/01/01
2222222222 부 아아아 1935/02/02
2222222222 ....................
원하는 결과
사번 관계 성명 인원 자녀수 생년월일 만나이 가족수당
1111111111 부 가가가 1 1930/05/02 80 20,000
1111111111 모 나나나 2 1935/07/08 75 20,000
1111111111 배우자 다다다 3 1980/01/01 40 40,000
1111111111 자녀 자자자 4 1995/05/05 21 0 <------------ 만 20세 이상으로 수당 제외
1111111111 자녀 라라라 5 1 2010/03/03 10 20,000
1111111111 자녀 마마마 6 2 2011/05/05 8 60,000
1111111111 자녀 바바바 7 3 2013/07/10 6 100,000
1111111111 자녀 사사사 8 4 2015/01/01 5 100,000
2222222222 부 아아아 1 1935/02/02 78 20,000
- 자녀는 생년월일 순으로 정렬
-만나이는 제가 임으로 이해를 돕기 위해 대략 넣은 것입니다.
답변 부탁 드립니다. 감사합니다.
with t (employeeno, relation, namekor, birthdate) as
(
select '1111111111','부', '가가가', to_date('1930-05-02','yyyy-mm-dd') from dual union all
select '1111111111','모', '나나나', to_date('1935-07-08','yyyy-mm-dd') from dual union all
select '1111111111','배우자', '다다다', to_date('1980-01-01','yyyy-mm-dd') from dual union all
select '1111111111','자녀', '자자자', to_date('1995-05-05','yyyy-mm-dd') from dual union all
select '1111111111','자녀', '라라라', to_date('2010-03-03','yyyy-mm-dd') from dual union all
select '1111111111','자녀', '마마마', to_date('2011-05-05','yyyy-mm-dd') from dual union all
select '1111111111','자녀', '바바바', to_date('2013-07-10','yyyy-mm-dd') from dual union all
select '1111111111','자녀', '사사사', to_date('2015-01-01','yyyy-mm-dd') from dual union all
select '2222222222','부', '아아아', to_date('1935-02-02','yyyy-mm-dd') from dual
)
select employeeno, relation, namekor, birthdate, age
, nvl(case when relation in ('조부','부') and age >= 60 then 20000
when relation in ('조모','모') and age >= 55 then 20000
when relation = '배우자' then 40000
when relation = '자녀' and age < 20 then decode(chdno,1,20000,2,60000,100000)
end,0) amt
from
(
select employeeno, relation, namekor, birthdate, age
, sum(case when relation = '자녀' and age < 20 then 1 end) over(partition by employeeno order by rn) chdno
from
(
select employeeno, relation, namekor, birthdate
,trunc(months_between(trunc(sysdate), birthdate) / 12) age
,rownum rn
from t
)
)
답변 너무너무 감사합니다.
) 버튼을 클릭하여 작성 하시면 됩니다.