마농님 어제 날짜조회 관련해서 하나 더 질문드려도 될까요? 0 7 1,489

by woni_rang [SQL Query] [2014.12.18 08:48:32]


어제 질문드렸던것이 

work_start_dt : 2014-12-09

work_end_dt : 2014-12-16

일 경우 조회 시작이 2014-12-10 조회 종료가 2014-12-15로 했을 경우

교차비교해서 하면 된다고 하셔서 도움을 받았는데요

데이타가 날짜만 있는게 아니고 시간도 있어서 그것도 조회가 되어야하는데요.

무슨 말이냐면 근무조가 A, B, C가 있고

        START_TIME     END_TIME

A조 : 06:00:00           13:59:59

B조 : 14:00:00           21:59:59

C조 : 22:00:00           05:59:59

이렇게 근무조 테이블에 시간정보 있고

만약 조회할때 근무조를 C조로 하고 조회일을 2014-12-05 ~2014-12-06으로 조회시..

work_start_dt : 2014-08-05 14:41:32

work_end_dt : 2014-08-06 15:01:32

이렇게 인경우 C가 data로는 검색에 직접 걸리지 않지만 work_time안에는 들어가기 때문에 검색이 되어야하는데

근무조 테이블에는 시간만 있어서 비교하기가 어렵네요..

다시 한번 도움 요청드립니다..(__)

 

 

 

 

by 마농 [2014.12.18 09:06:03]

work_start_dt, work_end_dt 가 저장된 테이블에 근무조 항목도 함께 저장되어 있나요?


C가 data로는 검색에 직접 걸리지 않지만 ===> 왜 안걸리죠?
work_time안에는 들어가기 때문에 검색이 되어야하는데 ===> 왜 그래야 하죠?


검색 조건에 맞는 자료를 찾는 규칙을 설명해 주세요.
예제 자료를 가지고요.


by woni_rang [2014.12.18 09:45:06]

우선..위에 날짜가 좀 틀렸었네요..죄송합니다.
work_start_dt,  work_end_dt가 저장된 테이블과 근무조가 있는 테이블은 다르구요.
C조가 근무시작시간이 22:00:00이고 근무종료시간이 05:59:59인데 
작업 시작시간 work_start_dt가 2014-12-05 14:41:32이고 작업종료시간 work_end_dt가 2014-12-06 15:01:32입니다

근무조 테이블에는 시간만 있어서 비교했을시 시간안에 포함이 안되서 안걸린다고 표현했습니다.
근데 실제로 작업시간은 5일 14:41:32부터 6일 15:01:32 이므로 C조도 근무시간이 포함이 되므로 
조회조건을 일자만 2014-12-05 ~ 2014-12-06 했을경우에는 나오지만 근무조를 C조를 넣을경우에는 나오지가 않습니다.
시간만 가지고 있는 근무조를 어떤식으로 비교해야 work_start_dt가 2014-12-05 14:41:32이고 작업종료시간 work_end_dt가 2014-12-06 15:01:32인 결과가 조회가 되는것인지입니다..

설명이 너무 부족해서 죄송합니다..

 


by 마농 [2014.12.18 09:52:00]

테이블에 근무조 항목이 있는지에 대한 답은 없네요. 없어서 설명 안한거겟죠?

시간안에 포함이 안되서 안걸린다??? 제 눈에는 포함되는 걸로 보이는데요?

work_start_dt,  work_end_dt 의 데이터 타입도 알려주세요.


by woni_rang [2014.12.18 09:56:48]

네 테이블에 포함이 안되어있어서 근무조 테이블에서 조회조건에 근무조 선택시 해당 근무조의 시간을 가져오고 있습니다.

타입은 DATE타입입니다.

근무조는 시간만 가지고 있어서 work_start_dt의 시간 14:41:32 work_end_dt의 시간 15:01:32 이렇게 비교를해서 포함이 안된다고 생각했습니다..

그리고 근무조 테이블의 start_time, end_time은 타입이 varchar2로 되어있습니다..


by 마농 [2014.12.18 10:54:00]
WITH master AS
(-- 근무내역
SELECT 1 id
     , TO_DATE('2014-12-05 14:41:32', 'yyyy-mm-dd hh24:mi:ss') work_start_dt
     , TO_DATE('2014-12-06 15:01:32', 'yyyy-mm-dd hh24:mi:ss') work_end_dt
  FROM dual
)
, code AS
(-- 근무조 시간
SELECT 'A' cd, '06:00:00' start_time, '13:59:59' end_time FROM dual
UNION ALL SELECT 'B', '14:00:00', '21:59:59' FROM dual
UNION ALL SELECT 'C', '22:00:00', '05:59:59' FROM dual
)
, condition AS
(-- 날짜 시간 합체 조건
SELECT TO_DATE(dt ||' '|| stm, 'yyyy-mm-dd hh24:mi:ss') sdt
     , TO_DATE(dt ||' '|| etm, 'yyyy-mm-dd hh24:mi:ss') edt
  FROM (-- 조건 날짜 분할
        SELECT TO_CHAR(sdt + LEVEL - 1, 'yyyy-mm-dd') dt
          FROM (SELECT TO_DATE('2014-12-05', 'yyyy-mm-dd') sdt -- 시작일
                     , TO_DATE('2014-12-06', 'yyyy-mm-dd') edt -- 종료일
                  FROM dual)
         CONNECT BY LEVEL <= edt - sdt + 1
        ) a
     , (-- 조건 시간 분할
        SELECT cd
             , DECODE(LEVEL, 1, start_time, '00:00:00') stm
             , CASE WHEN start_time > end_time AND LEVEL = 1
                    THEN '23:59:59' ELSE end_time END etm
          FROM (SELECT * FROM code WHERE cd = 'C') -- 근무조
         CONNECT BY LEVEL <= CASE WHEN start_time > end_time THEN 2 ELSE 1 END
        ) b
)
SELECT *
  FROM master m
 WHERE EXISTS (SELECT 1
                 FROM condition c
                WHERE m.work_start_dt <= c.edt
                  AND m.work_end_dt   >= c.sdt
               )
;

 


by woni_rang [2014.12.18 15:58:03]

기존 쿼리가 복잡하게 되어있어서 적용하는데 애먹었지만

잘 응용해서 수정했습니다.

결과는 원하는데로 나왔는데 통과가 되었으면 하네요.

정말 감사드립니다.(__)


by jkson [2014.12.18 15:16:56]

마농님 항상 수고하십니다.

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