꿈꾸는 개발자, DBA 커뮤니티 구루비

질문 드려요

by 냉동닭 [SQL Query] 시간 계산 휴일제외 엽무시간 [2018.04.05 17:38:13]

안녕하세요. 매일 눈팅만 하다가 처음으로 질문글 등록하네요.

다름아니라 시간계산을 위한 쿼리를 작성하려고 하는데 감이 안잡혀서요.

작성하려는 쿼리의 목적은 질문이 올라오면 답변을하는데 걸리는 시간을 구하려는겁니다.

단 시간은 업무시간내(09:00~18:00)에만 측정되고, 업무시간 외 및 휴무일(토,일,공휴일)에 등록된 걸은 돌아오는 업무일의 09:00로 기본을 잡으려 합니다.

단순히 쿼리만으로 위 내용을 작성할 수 있을까요?

짱구를 굴려봐도 쉽지가 않네요

도움 부탁드리겠습니다.

by 마농 [2018.04.05 18:03:40]

1. 점심시간도 빼야죠?
2. 요청시간, 완료시간의 자료형은 뭔가요? date ? varchar2 ? 문자형이라면 저장 포멧은?
3. 달력 테이블도 관리하고 있는거겠죠?

by 냉동닭 [2018.04.05 18:05:42]

앗.. 점심시간도 빼야할 것 같네요ㅎㅎ

by 냉동닭 [2018.04.05 18:18:19]

1. 점심시간도 넣으면 좋겠지만 복잡하다면 넣지 않아도 됩니다.

2. 요청시간은 CHAR(ex. 20180405181720), 완료시간은 DATA 입니다.

3. 달력 테이블은 따로 관리하지 않습니다..ㅠㅠ

by 신이만든지기 [2018.04.05 18:20:22]

우리나라 연휴(설, 추석 등)나 임시공휴일 등을 계산하기 위해서 필히 달력테이블을 만드셔야 할 것 같습니다.

by 마농 [2018.04.05 18:21:07]

달렬 테이블 있어야 하는데요?
달력 테이블 없으면 휴일 테이블이라도 있어야 하는데요?
달력 테이블에 휴일관리를 하는게 좋습니다.

by 마농 [2018.04.05 18:53:03]

WITH calendar_t AS
(
SELECT '20180430' dt, 'N' hu_yn FROM dual
UNION ALL SELECT '20180501', 'Y' FROM dual
UNION ALL SELECT '20180502', 'N' FROM dual
UNION ALL SELECT '20180503', 'N' FROM dual
UNION ALL SELECT '20180504', 'N' FROM dual
UNION ALL SELECT '20180505', 'Y' FROM dual
UNION ALL SELECT '20180506', 'Y' FROM dual
UNION ALL SELECT '20180507', 'Y' FROM dual
UNION ALL SELECT '20180508', 'N' FROM dual
)
, data_t AS
(
SELECT 1 pk, '20180430083000' req_dt, TO_DATE('2018050215', 'yyyymmddhh24') end_dt FROM dual
UNION ALL SELECT 2, '20180430163000', TO_DATE('2018050215', 'yyyymmddhh24') FROM dual
UNION ALL SELECT 3, '20180501063000', TO_DATE('2018050115', 'yyyymmddhh24') FROM dual
UNION ALL SELECT 4, '20180502063000', TO_DATE('2018050215', 'yyyymmddhh24') FROM dual
)
SELECT a.pk, a.req_dt, a.end_dt
     , NVL(TRUNC(
       SUM( GREATEST(0, LEAST(edt, edt1) - GREATEST(sdt, sdt1)) *24*60
          + GREATEST(0, LEAST(edt, edt2) - GREATEST(sdt, sdt2)) *24*60
          )
       ), 0) AS minutes
  FROM (SELECT pk, req_dt, end_dt
             , TO_DATE(req_dt, 'yyyymmddhh24miss') sdt
             , end_dt edt
          FROM data_t
        ) a
     , (SELECT dt
             , hu_yn
             , TO_DATE(dt || '09', 'yyyymmddhh24') sdt1
             , TO_DATE(dt || '12', 'yyyymmddhh24') edt1
             , TO_DATE(dt || '13', 'yyyymmddhh24') sdt2
             , TO_DATE(dt || '18', 'yyyymmddhh24') edt2
          FROM calendar_t
        ) b
 WHERE b.dt(+) >= TO_CHAR(a.sdt, 'yyyymmdd')
   AND b.dt(+) <= TO_CHAR(a.edt, 'yyyymmdd')
   AND b.hu_yn(+) = 'N'
 GROUP BY a.pk, a.req_dt, a.end_dt
 ORDER BY pk
;

by 냉동닭 [2018.04.05 23:22:24]

헉! 감사합니다.. 설마 다 짜주실 줄은...

작성해 주신거 뜯어보고 공부해서 적용해 보도록 하겠습니다.

감사합니다~

댓글등록

SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code(

) 버튼을 클릭하여 작성 하시면 됩니다.

로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입

데이터베이스 질문과 답변