CONNECT BY LEVEL 구문 질문드립니다. 0 5 1,166

by Oracle10g [SQL Query] [2019.03.12 11:21:35]


안녕하십니까 구루비 여러분 ㅠㅠ 
오랜만에 인사드리는 것 같습니다..ㅎㅎ
요즘 밤낮 온도차이가 큰 것 같습니다. 환절기 감기 조심하십시요^^

CONNECT BY LEVEL 구문을 사용하여 
억지로 결과를 뽑긴했는데..

구루비 회원분들이라면 분명 더 좋은 방법을 찾으셨을 것 같아.. 질문드립니다.ㅠㅠ

원하는 결과값과 샘플데이터 보여드립니다!! 감사합니다!!

 


WITH BREAK AS ( --휴가기간을 관리하는 테이블
     SELECT '1111' EMPNO,'2019-01-01' STR_DT ,'2019-02-24' END_DT FROM DUAL UNION ALL
     SELECT '2222' EMPNO,'2019-02-10' STR_DT ,'2019-02-28' END_DT FROM DUAL     
)

SELECT * FROM BREAK;


--원하는 데이터  (H: 휴가, NULL : 정상근무)

/*
1111     2019-02-01       H
1111     2019-02-01       H
...
1111     2019-02-24       H
1111     2019-02-25       NULL
1111     2019-02-26       NULL 
1111     2019-02-27       NULL 
1111     2019-02-28       NULL 
   
2222     2019-02-01       NULL
2222     2019-02-02       NULL
...
2222     2019-02-10       H
2222     2019-02-11       H
...
2222     2019-02-28       H

*/
        


 

by 마농 [2019.03.12 11:26:46]

작성하신 쿼리를 올려주셔야.
해당 쿼리가 효율적인지 아닌지?
더 좋은 쿼리가 가능한지? 등을 알 수 있을 듯 하네요.


by Oracle10g [2019.03.12 11:48:07]
WITH BREAK AS ( --휴가기간을 관리하는 테이블
     SELECT '1111' EMPNO,'2019-01-01' STR_DT ,'2019-02-24' END_DT FROM DUAL UNION ALL
     SELECT '2222' EMPNO,'2019-02-10' STR_DT ,'2019-02-28' END_DT FROM DUAL     
)
 , CALENDAR AS( 
  SELECT TO_DATE(TO_CHAR(TRUNC(TO_DATE(&YYYYMMDD), 'MM') + LEVEL - 1 ,'YYYYMMDD'),'YYYY-MM-DD') DAL_DT
    FROM DUAL
 CONNECT BY LEVEL <= TO_NUMBER(TO_CHAR(LAST_DAY(TRUNC(TO_DATE(&YYYYMMDD), 'MM')),'DD'))
)
 SELECT A.*,B.*,CASE WHEN A.DAL_DT BETWEEN STR_DT AND END_DT THEN 'H' ELSE NULL END GB
   FROM CALENDAR A
       ,BREAK B
  ORDER BY 2,1;
              
 

죄송합니다.마농님~~

그저 회원분들은 어떤식으로 구현을 하실지 궁금했습니다.

 


by 마농 [2019.03.12 12:19:32]

1. 크로스 조인으로 인한 문제
  - 조인 조건이 없으므로 인해 비효율 발생 -> 조건 추가 필요
  - 사원별 휴가가 2번 이상 있는 경우 잘못된 결과 발생(일자 중복) 가능
2. 휴일 테이블 사용으로 인한 문제
  - 휴가를 안간 직원은 정상근무 표시 안됨
  - 출력 대상을 한정지을 사원 테이블 필요
3. 근무 타입 표현 부족
  - 휴가와 정상근무만 존재하는데?
  - 휴가가 아닌 휴일(주말, 공휴일) 정보도 필요할 듯 하고?
  - 중도 퇴사자나 중도입사자의 경우 표현도 필요할 듯.
  - 예 : 2/15 퇴사자의 경우, 2/15 까지는 정상근무 2/26일부터는 퇴직으로 표현

* 문제 사항 추가 *
4. 날짜 변환 함수의 잘못된 사용법
  - 날짜 변환시 포멧 지정 필수입니다. 안하면 에러 가능성 있습니다.
  - TO_DATE('20190201')  ===>  TO_DATE('20190201', 'yyyymmdd')
5. 비교식도 마찬가지입니다.
  - 날짜와 문자를 비교하면 자동형변환 되면서 오류 가능성 있습니다.
  - dal_dt 는 날짜형, str_dt 와 end_dt 는 문자형.
  - 비교시 형식을 맞춰주세요.


by Oracle10g [2019.03.12 13:29:06]

감사합니다 마농님 ㅠㅠ
테이블이 휴직에 대한 사항인데 일자가 겹치진 않습니다만
말씀해주신 사항 참고하여 수정해보겠습니다.
월중 두개의 휴직데이터가 있으면 문제가 또 발생 하겠네요 ㅠㅠ
수정하겠습니다
감사합니다!!


by 마농 [2019.03.12 13:37:18]
WITH break AS
(
SELECT '1111' empno, '2019-01-01' str_dt, '2019-02-24' end_dt FROM dual
UNION ALL SELECT '2222', '2019-02-02', '2019-02-02' FROM dual
UNION ALL SELECT '2222', '2019-02-10', '2019-02-28' FROM dual
)
, calendar AS
(
SELECT TO_CHAR(dt + LEVEL - 1, 'yyyy-mm-dd') dal_dt
  FROM (SELECT TRUNC(TO_DATE('20190215', 'yyyymmdd'), 'mm') dt FROM dual)
 CONNECT BY LEVEL <= LAST_DAY(dt) - dt + 1
)
, emp AS
(
SELECT '1111' empno, 'AAAA' ename FROM dual
UNION ALL SELECT '2222', 'BBBB' FROM dual
UNION ALL SELECT '3333', 'CCCC' FROM dual
)
SELECT e.empno
     , c.dal_dt
     , NVL2(b.empno, 'H', '') gb
  FROM emp e
 CROSS JOIN calendar c
  LEFT OUTER JOIN break b
    ON e.empno = b.empno
   AND c.dal_dt BETWEEN b.str_dt AND b.end_dt
 ORDER BY empno, dal_dt
;

 

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