database.sarang.net
UserID
Passwd
Database
DBMS
MySQL
PostgreSQL
Firebird
ㆍOracle
Informix
Sybase
MS-SQL
DB2
Cache
CUBRID
LDAP
ALTIBASE
Tibero
DB 문서들
스터디
Community
공지사항
자유게시판
구인|구직
DSN 갤러리
도움주신분들
Admin
운영게시판
최근게시물
Oracle Q&A 41259 게시물 읽기
No. 41259
월말합계, 예외관리 조인관련 쿼리 문의
작성자
IT재벌(IT재벌)
작성일
2016-10-13 11:27
조회수
5,958

부서별, 직원들에게 지급된 금액을 월별로 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

 

도움 부탁드리겠습니다.

이 글에 대한 댓글이 총 2건 있습니다.

SELECT bonbu
     , buseo
     , SUM(DECODE(m,  1, cnt)) cur01_cnt
     , SUM(DECODE(m,  1, amt)) cur01_mon
     , SUM(DECODE(m,  2, amt)) cur02_mon
     , SUM(DECODE(m,  3, amt)) cur03_mon
     , SUM(DECODE(m,  4, amt)) cur04_mon
     , SUM(DECODE(m,  5, amt)) cur05_mon
     , SUM(DECODE(m,  6, amt)) cur06_mon
     , SUM(DECODE(m,  7, amt)) cur07_mon
     , SUM(DECODE(m,  8, amt)) cur08_mon
     , SUM(DECODE(m,  9, amt)) cur09_mon
     , SUM(DECODE(m, 10, amt)) cur10_mon
     , SUM(DECODE(m, 11, amt)) cur11_mon
     , SUM(DECODE(m, 12, amt)) cur12_mon
  FROM (SELECT et.bonbu
             , et.buseo
             , sd.m
             , COUNT(DISTINCT et.emp_no) cnt
             , SUM(pa.pay_amt)           amt
          FROM pay_ymd      py -- 날짜테이블
             , employee_tbl et -- 사원테이블
             , pay_amt      pa -- 지급금액테이블
             , (SELECT LEVEL m
                     , TO_CHAR(ADD_MONTHS(ym, 1-LEVEL), 'yyyymm') ym
                  FROM (SELECT TO_DATE('201609', 'yyyymm') ym FROM dual)
                 CONNECT BY LEVEL <= 12
                ) sd
         WHERE py.pay_ym     = sd.ym
           AND pa.pay_ym_key = py.pay_ym_key
           AND pa.emp_no     = et.emp_no
           AND pa.pay_amt   <> 0
         GROUP BY et.bonbu, et.buseo, sd.m
         UNION ALL
        SELECT bt.bonbu
             , bt.buseo
             , sd.m
             , bt.add_cnt cnt
             , bt.add_amt amt
          FROM b_tbl        bt -- 지급추가테이블
             , (SELECT LEVEL m
                     , TO_CHAR(ADD_MONTHS(ym, 1-LEVEL), 'yyyymm') ym
                  FROM (SELECT TO_DATE('201609', 'yyyymm') ym FROM dual)
                 CONNECT BY LEVEL <= 12
                ) sd
         WHERE bt.std_ym     = sd.ym
        )
 GROUP BY bonbu, buseo
;

마농(manon94)님이 2016-10-13 13:45에 작성한 댓글입니다.
이 댓글은 2016-10-13 15:48에 마지막으로 수정되었습니다.

마농님 감사합니다.

항상 많은 도움을 받네요

IT재벌(IT재벌)님이 2016-10-13 15:43에 작성한 댓글입니다.
[Top]
No.
제목
작성자
작성일
조회
41262sequence does not exist 오류...
문종훈
2016-10-18
6060
41261닷넷에서 오라클db 연동... [1]
문종훈
2016-10-17
6404
41260오라클 merge문에서 function 사용이 안되나요? [2]
정성식
2016-10-14
6352
41259월말합계, 예외관리 조인관련 쿼리 문의 [2]
IT재벌
2016-10-13
5958
41257주소 기반 검색 관련 문의
최인수
2016-10-10
5660
41256오라클(토드) 검색 자동완성 기능에 대하여...
허광민
2016-10-07
6171
41253컬럼간 순위를 구하고 싶습니다 [1]
궁굼이
2016-10-04
6533
Valid XHTML 1.0!
All about the DATABASE... Copyleft 1999-2023 DSN, All rights reserved.
작업시간: 0.049초, 이곳 서비스는
	PostgreSQL v16.1로 자료를 관리합니다