53주(1년)간 인턴들 수련부서를 관리하는 프로그램을 개발중입니다..
ER_RESIWEEKDEPT 라는 테이블에 사원별(EMPNO) 시작일자(STRYMD)에 따른 주차별(WEEK) 부서(TRAINDEPT)를 저장받았습니다.
문제는 스크린샷 같이 달력에 표시를 해줘야하는데 제가 아직 실력이 많이 부족한지라 엄두조차 나지 않는 상황입니다ㅠㅠ
도움을 주시면 정말 감사하겠습니다 ㅠㅠ
<스크린샷 설명>
500915 사원은 시작일자가 2017-03-01 이다. 따라서 9월 달력을 조회했을때 27주차부터 시작되는 근무데이터가 조회되어야함..
500916 사원은 시작일자가 2017-09-13 이다. 따라서 9월 달력을 조회했을때 13일부터 1주차~에 해당하는 근무데이터가 조회되어야함..
<조건>
- 모든 주차는 ER_CALENDAR의 일(1)~토(7) 개념과 다르게 월(2)~일(1) 기준입니다.
(예시: 2017-01-01 : 1주차
2017-01-02 ~ 2017-01-08 : 2주차)
WITH er_calendar AS ( SELECT TO_CHAR(ym + LEVEL - 1, 'yyyymmdd') ymd FROM (SELECT TO_DATE('201709', 'yyyymm') ym FROM dual) CONNECT BY LEVEL <= LAST_DAY(ym) - ym + 1 ) , er_resiweekdept AS ( SELECT '500915' empno, '20170301' strymd, '27' week, 'GS' traindept FROM dual UNION ALL SELECT '500915', '20170301', '28', 'DT' FROM dual UNION ALL SELECT '500915', '20170301', '29', 'LM' FROM dual UNION ALL SELECT '500915', '20170301', '30', 'XX' FROM dual UNION ALL SELECT '500915', '20170301', '31', 'YY' FROM dual UNION ALL SELECT '500916', '20170913', '01', 'GS' FROM dual UNION ALL SELECT '500916', '20170913', '02', 'DT' FROM dual UNION ALL SELECT '500916', '20170913', '03', 'LM' FROM dual ) SELECT * FROM (SELECT * FROM (SELECT b.empno , TO_NUMBER(SUBSTR(a.ymd, 7, 2)) dd , b.week 주차 , b.traindept 배치부서 FROM er_calendar a , er_resiweekdept b WHERE a.ymd LIKE '201709' || '%' AND a.ymd >= b.strymd AND a.ymd >= TO_CHAR(TRUNC(TO_DATE(b.strymd, 'yyyymmdd'), 'iw') + b.week*7-7, 'yyyymmdd') AND a.ymd <= TO_CHAR(TRUNC(TO_DATE(b.strymd, 'yyyymmdd'), 'iw') + b.week*7-1, 'yyyymmdd') ) UNPIVOT (v FOR 구분 IN (배치부서, 주차) ) UNION ALL SELECT TO_CHAR(TO_DATE(ymd, 'yyyymmdd'), ' yyyy"년"mm"월"') empno , TO_NUMBER(SUBSTR(ymd, 7, 2)) dd , '요일' 구분 , TO_CHAR(TO_DATE(ymd, 'yyyymmdd'), 'dy') v FROM er_calendar WHERE ymd LIKE '201709' || '%' ) PIVOT (MIN(v) FOR dd IN ( 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 , 11, 12, 13, 14, 15, 16, 17, 18, 19, 20 , 21, 22, 23, 24, 25, 26, 27, 28, 29, 30 , 31 ) ) ORDER BY empno, 구분 ;
/*ER_RESIWEEKDEPT 주차의 년도가 안나와있네요. 임의로 YMD 기준으로 하겠습니다. */ WITH ER_CALENDAR ( YMD , ORGCD , DAY ) AS ( SELECT '20170901', '1', '6' FROM DUAL UNION ALL SELECT '20170902', '1', '7' FROM DUAL UNION ALL SELECT '20170903', '1', '1' FROM DUAL UNION ALL SELECT '20170904', '1', '2' FROM DUAL UNION ALL SELECT '20170905', '1', '3' FROM DUAL UNION ALL SELECT '20170906', '1', '4' FROM DUAL UNION ALL SELECT '20170907', '1', '5' FROM DUAL UNION ALL SELECT '20170908', '1', '6' FROM DUAL UNION ALL SELECT '20170909', '1', '7' FROM DUAL UNION ALL SELECT '20170910', '1', '1' FROM DUAL UNION ALL SELECT '20170911', '1', '2' FROM DUAL UNION ALL SELECT '20170912', '1', '3' FROM DUAL UNION ALL SELECT '20170913', '1', '4' FROM DUAL UNION ALL SELECT '20170914', '1', '5' FROM DUAL UNION ALL SELECT '20170915', '1', '6' FROM DUAL UNION ALL SELECT '20170916', '1', '7' FROM DUAL UNION ALL SELECT '20170917', '1', '1' FROM DUAL UNION ALL SELECT '20170918', '1', '2' FROM DUAL UNION ALL SELECT '20170919', '1', '3' FROM DUAL UNION ALL SELECT '20170920', '1', '4' FROM DUAL UNION ALL SELECT '20170921', '1', '5' FROM DUAL UNION ALL SELECT '20170922', '1', '6' FROM DUAL UNION ALL SELECT '20170923', '1', '7' FROM DUAL UNION ALL SELECT '20170924', '1', '1' FROM DUAL UNION ALL SELECT '20170925', '1', '2' FROM DUAL UNION ALL SELECT '20170926', '1', '3' FROM DUAL UNION ALL SELECT '20170927', '1', '4' FROM DUAL UNION ALL SELECT '20170928', '1', '5' FROM DUAL UNION ALL SELECT '20170929', '1', '6' FROM DUAL UNION ALL SELECT '20170930', '1', '7' FROM DUAL ) , ER_RESIWEEKDEPT ( EMPNO , STRYMD, WEEK , TRAINDEPT ) AS ( SELECT '500915','20170301','35','GS' FROM DUAL UNION ALL SELECT '500915','20170301','36','GS' FROM DUAL UNION ALL SELECT '500915','20170301','37','DT' FROM DUAL UNION ALL SELECT '500915','20170301','38','GS' FROM DUAL UNION ALL SELECT '500916','20170301','35','DT' FROM DUAL UNION ALL SELECT '500916','20170301','36','DT' FROM DUAL UNION ALL SELECT '500916','20170301','37','DT' FROM DUAL UNION ALL SELECT '500916','20170301','38','GS' FROM DUAL ) --SELECT TO_CHAR(TO_DATE(A.YMD),'IW') FROM ER_CALENDAR A SELECT * FROM (SELECT B.EMPNO , DECODE(LV,1,B.TRAINDEPT,2,TO_CHAR(TO_DATE(A.YMD),'IW')) VAL , SUBSTR(A.YMD,7,2) DD , LV FROM ER_CALENDAR A , ER_RESIWEEKDEPT B , (SELECT LEVEL LV FROM DUAL CONNECT BY LEVEL <= 2 ) WHERE TO_CHAR(TO_DATE(A.YMD),'IW') = B.WEEK(+) AND B.STRYMD(+) <= A.YMD AND A.YMD BETWEEN '20170901' AND '20170930' AND B.TRAINDEPT IS NOT NULL ) PIVOT ( MIN(VAL) FOR DD IN ( '01','02','03','04','05','06','07','08','09','10' ,'11','12','13','14','15','16','17','18','19','20' ,'21','22','23','24','25','26','27','28','29','30' ) ) ORDER BY EMPNO , LV