쿼리 질문드립니다ㅠㅠ도와주십시요 0 7 799

by Oracle10g [SQL Query] [2017.09.06 17:33:00]


Screenshot_16.jpg (69,400Bytes)
Screenshot_18.jpg (117,812Bytes)
Screenshot_19.jpg (152,100Bytes)

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주차)

by 우리집아찌 [2017.09.06 18:08:04]

9월만 조회인가요?


by Oracle10g [2017.09.07 09:10:48]

아닙니다 아찌님ㅠㅠ 파라미터로 ER_CALENDAR 에 년월 조건걸고싶습니다 ㅠㅠ


by 우리집아찌 [2017.09.07 10:13:54]

아 늦었따... 마농님처럼 되면 안됩니다.

제가 먹고살기 힘들어집니다. ㅎㅎ


by 마농 [2017.09.07 09:17:32]
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, 구분
;

 


by Oracle10g [2017.09.07 09:36:08]

지금 제 실력으로는 마농님이 짜주신 쿼리를 분석하는것 조차 힘드네요......

하나하나 꼼꼼하게 분석하여 마농님처럼 될수있도록...열심히하겠습니다 감사합니다 마농님!!

 

좋은하루 되십시요 ^^


by 우리집아찌 [2017.09.07 10:11:22]
/*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 

 


by Oracle10g [2017.09.07 11:01:35]

아찌님!!감사합니다 ㅎㅎㅎ

잘 분석해서 참고하도록 하겠습니다^^

좋은하루되십시요!!!

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입