부서별, 직원들에게 지급된 금액을 월별로 count 및 sum을 구하고자 합니다.
날짜테이블, 사원테이블, 지급금액테이블을 조인하여 하나의 테이블을 형성합니다. (예시 : tot)
본부 부서 사번 년월 금액
인사본부 인사부 001 201610 1000
인사본부 인사부 001 201610 2000
인사본부 인사부 001 201609 3000
인사본부 인사부 001 201608 3000
인사본부 인사부 002 201610 4000
인사본부 인사부 002 201609 4000
인사본부 인사부 002 201607 5000
기획본부 기획부 003 201610 5000
기획본부 기획부 003 201609 4000
기획본부 기획부 003 201609 4000
기획본부 기획부 004 201610 5000
기획본부 기획부 004 201609 3000
기획본부 기획부 004 201608 3000
B_TBL 은 예외자 관리 테이블입니다.
본부 부서 년월 인원 금액
인사본부 인사부 201610 3 10000
인사본부 인사부 201609 2 15000
인사본부 인사부 201608 4 25000
기획본부 기획부 201609 3 15000
기획본부 기획부 201608 5 30000
인원의 경우, 첫번째 조인테이블(TOT) 에서 기준월기준, 부서별로 인원을 카운트 한후, 예외자 관리 테이블에 해당월 인원을 더해줍니다.
금액의 경우, 첫번째 조인테이블(TOT)에서 기준월기준, 부서별 금액을 SUM한후, 예외자 관리 테이블에서 해당월 금액을 더해줍니다.
과거의 년월도 예외자 관리 테이블에서 해당 년월에 맞게 더해줍니다.
원하는 쿼리 결과는 아래와 같습니다. (기준월 : 201610)
부서 인원 201610 201609 ---- 201509 합계 평균
인사부 5 17000 22000 12개월 합계 월 평균금액
기획부 2 10000 26000
제가 만들어본 쿼리는 아래와 같은데, 여기에 한번더 Select from을 해줘야 되는 것 같기도 하고, group by에서 오류가 발생되기도 합니다.
무식하게 점점 길어지는 것 같아서, 간결하게 이쁘게 만들어 보고 싶습니다.
SELECT BONBU, BUSEO,
COUNT(DECODE(MM,'01',EMP_NO)) + (SELECT NVL(ADD_CNT) FROM B_TBL WHERE STD_YM = PAY_YM AND BUSEO = TOT.BUSEO) AS CUR_MON, -- 당월 인원수 계
SUM((DECODE(MM,'01',PAY_YM)) + (SELECT NVL(ADD_AMT) FROM B_TBL WHERE STD_YM = PAY_YM AND BUSEO = TOT.BUSEO) AS CUR01_MON, -- 당월 지급금액 계
SUM((DECODE(MM,'02',PAY_YM)) + (SELECT NVL(ADD_AMT) FROM B_TBL WHERE STD_YM = PAY_YM AND BUSEO = TOT.BUSEO) AS CUR02_MON, -- 1월전 지급금액 계
SUM((DECODE(MM,'03',PAY_YM)) + (SELECT NVL(ADD_AMT) FROM B_TBL WHERE STD_YM = PAY_YM AND BUSEO = TOT.BUSEO) AS CUR03_MON, -- 2월전 지급금액 계
---
FROM (
SELECT ET.BONBU,
ET.BUSEO,
PY.PAY_YM,
ET.EMPL_NO,
PA.PAY_AMT
FROM PAY_YMD PY, -- 날짜테이블
EMPLOYEE_TBL ET, -- 사원테이블
PAY_AMT PA, -- 지급금액테이블
(SELECT LPAD(LEVEL, 2, '0') MM,
TO_CHAR(ADD_MONTHS(YM, -LEVEL+1), 'YYYYMM') SD,
LAST_DAY(YM) YM
FROM (SELECT TO_DATE('201609', 'YYYYMM') YM FROM DUAL)
CONNECT BY LEVEL <= 12
) SDT
WHERE PY.PAY_YM_KEY = PA.PAY_YM_KEY
AND ET.EMP_NO = PPA.EMP_NO
AND PY.PAY_YM BETWEEN TO_CHAR(ADD_MONTHS(YM,-11),'YYYYMM') AND TO_CHAR(YM,'YYYYMM') -- 12개월치
AND PY.PAY_YM = SD
AND PA.PAY_AMT <> 0
) TOT
GROUP BY BONBU, BUSEO
도움 부탁드리겠습니다.
|