안녕하세요 쿼리작성하다가 막힐때 여기서 도움을 많이 받고있어 항상 감사하게 생각합니다. 인사업무 개발 중 부서별 직원의 현황을 조회하는 화면을 개발중인데 잘안되는 부분이 있어 질문올립니다.
<기준일 : 2014-01-01>
회사명 | 부서코드 | 부서명 | 사원(남) | 사원(여) | 주임(남) | 주임(여) | 대리(남) | 대리(여) | 과장(남) | 과장(여) | ... | |||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
테스트회사 | DEPT01 | 테스트부서 | 0 | 1 | 1 | 0 | 2 | 1 | 1 | 0 |
이런식으로 구성되는 화면입니다. 관련테이블은 발령정보, 사원정보 테이블이며 조회조건의 기준일자 시점의 재직사원의 현황을 보고자 합니다. 발령테이블의 발령시작일자 ~ 발령종료일자 시점의 재직부서를 가져오려하는데 제가 초안으로 작성한 SQL은 잘못된것같아서 고수님들이 조언을 얻고싶습니다. SELECT 회사명 ,부서코드 ,부서명 ,NVL(MAX(CASE WHEN CD = '001' THEN M_CNT END),0) AS M_CD1 ,NVL(MAX(CASE WHEN CD = '001' THEN F_CNT END),0) AS F_CD1 ,NVL(MAX(CASE WHEN CD = '002' THEN M_CNT END),0) AS M_CD2 ,NVL(MAX(CASE WHEN CD = '002' THEN F_CNT END),0) AS F_CD2 ,NVL(MAX(CASE WHEN CD = '003' THEN M_CNT END),0) AS M_CD3 ,NVL(MAX(CASE WHEN CD = '003' THEN F_CNT END),0) AS F_CD3 ,NVL(MAX(CASE WHEN CD = '004' THEN M_CNT END),0) AS M_CD4 ,NVL(MAX(CASE WHEN CD = '004' THEN F_CNT END),0) AS F_CD4 ,NVL(MAX(CASE WHEN CD = '005' THEN M_CNT END),0) AS M_CD5 ,NVL(MAX(CASE WHEN CD = '005' THEN F_CNT END),0) AS F_CD5 ,NVL(MAX(CASE WHEN CD = '006' THEN M_CNT END),0) AS M_CD6 ,NVL(MAX(CASE WHEN CD = '006' THEN F_CNT END),0) AS F_CD6 ,NVL(MAX(CASE WHEN CD = '007' THEN M_CNT END),0) AS M_CD7 ,NVL(MAX(CASE WHEN CD = '007' THEN F_CNT END),0) AS F_CD7 ,NVL(MAX(CASE WHEN CD = '008' THEN M_CNT END),0) AS M_CD8 ,NVL(MAX(CASE WHEN CD = '008' THEN F_CNT END),0) AS F_CD8 ,NVL(MAX(CASE WHEN CD = '009' THEN M_CNT END),0) AS M_CD9 ,NVL(MAX(CASE WHEN CD = '009' THEN F_CNT END),0) AS F_CD9 ,NVL(MAX(CASE WHEN CD = '010' THEN M_CNT END),0) AS M_CD10 ,NVL(MAX(CASE WHEN CD = '010' THEN F_CNT END),0) AS F_CD10 FROM ( SELECT B.회사명 ,B.부서코드 ,B.부서명 ,A.직위코드 ,A.직위코드명 ,SUM(NVL(B.M_CNT,0)) AS M_CNT ,SUM(NVL(B.F_CNT,0)) AS F_CNT FROM (SELECT 직위코드 ,직위코드명 FROM 공통코드테이블 WHERE CD_ID = '직위' AND USE_YN = 'Y' AND CMON_CD <= '010' --사원에서 대표이사까지만 조회하기 위함 ) A ,( SELECT A.회사명 ,A.발령후부서코드 ,A.발령후부서명 ,C.직위코드 ,COUNT(DECODE(C.성별,1,1,NULL)) AS M_CNT ,COUNT(DECODE(C.성별,2,1,NULL)) AS F_CNT FROM 발령테이블 A, 회사코드테이블 B, 사원테이블 C WHERE A.발령후부서코드 = C.부서코드 AND A.회사코드 = C.회사코드 AND A.회사코드 = B.회사코드 AND B.회사코드 = '테스트회사' AND A.발령후부서코드 = '테스트부서' AND '20140101' BETWEEN A.발령시작일 AND NVL(A.발령종료일,'99991231') GROUP BY A.회사코드 ,A.발령후부서코드 ,A.발령후부서명 ,C.직위코드 ) B WHERE A.직위코드 = B.직위코드 GROUP BY A.직위코드 ,A.직위명 ,B.회사명 ,B.부서코드 ,B.부서명 ) GROUP BY 회사명 , 부서코드,부서명 ORDER BY 부서코드
SELECT a.회사코드 , a.후부서코드 , COUNT(DECODE(a.후직위코드||b.성별, '0011', 1)) cd001_m , COUNT(DECODE(a.후직위코드||b.성별, '0012', 1)) cd001_f , COUNT(DECODE(a.후직위코드||b.성별, '0021', 1)) cd002_m , COUNT(DECODE(a.후직위코드||b.성별, '0022', 1)) cd002_f , COUNT(DECODE(a.후직위코드||b.성별, '0031', 1)) cd003_m , COUNT(DECODE(a.후직위코드||b.성별, '0032', 1)) cd003_f , COUNT(DECODE(a.후직위코드||b.성별, '0041', 1)) cd004_m , COUNT(DECODE(a.후직위코드||b.성별, '0042', 1)) cd004_f , COUNT(DECODE(a.후직위코드||b.성별, '0051', 1)) cd005_m , COUNT(DECODE(a.후직위코드||b.성별, '0052', 1)) cd005_f , COUNT(DECODE(a.후직위코드||b.성별, '0061', 1)) cd006_m , COUNT(DECODE(a.후직위코드||b.성별, '0062', 1)) cd006_f , COUNT(DECODE(a.후직위코드||b.성별, '0071', 1)) cd007_m , COUNT(DECODE(a.후직위코드||b.성별, '0072', 1)) cd007_f , COUNT(DECODE(a.후직위코드||b.성별, '0081', 1)) cd008_m , COUNT(DECODE(a.후직위코드||b.성별, '0082', 1)) cd008_f , COUNT(DECODE(a.후직위코드||b.성별, '0091', 1)) cd009_m , COUNT(DECODE(a.후직위코드||b.성별, '0092', 1)) cd009_f , COUNT(DECODE(a.후직위코드||b.성별, '0101', 1)) cd010_m , COUNT(DECODE(a.후직위코드||b.성별, '0102', 1)) cd010_f FROM 발령테이블 a , 사원테이블 b WHERE a.회사코드 = b.회사코드 AND a.사번 = b.사번 AND a.회사코드 = :v_회사코드 AND :v_기준일 BETWEEN a.발령시작일 AND NVL(a.발령종료일, '99991231') GROUP BY a.회사코드, a.후부서코드 ;