데이터베이스 질문과 답변
안녕하세요..
실 수행시간을 계산하고 싶어서 그러는데요..
오라클 함수를 이용하려고 합니다..
변수로는 시작시간, 종료시간을 받으려고 하고요..
함수는 기본적으로 종료시간 - 시작시간을 분으로 받으려고 합니다..
다만, 휴게시간을 포함하고 싶은데요..
휴게시간은 테이블로 관리를 할 계획입니다..
휴계시간 테이블은
휴게시작시간 휴게종료시간
0000 0100
1200 1300
이런식으로 관리하려고 하고 있습니다..
만약 작업시간이 2017-06-12 11:00 부터 2017-06-14 11:00 라고 하면
12일 12시간(휴게시간 1시간 제외)
13일 22시간(휴게시간 2시간 제외)
14일 10시간(휴게시간 1시간 제외)
로 해서 총 43시간을 분으로 받고 싶은데요..
어떻게 짜야 할지 모르겠네요.. 도와주세요..
WITH T AS (
SELECT TO_DATE('20170612 1200', 'YYYYMMDD HH24MI') BTIME, TO_DATE('20170612 1300', 'YYYYMMDD HH24MI') ETIME FROM DUAL UNION ALL
SELECT TO_DATE('20170613 1200', 'YYYYMMDD HH24MI') BTIME, TO_DATE('20170613 1300', 'YYYYMMDD HH24MI') ETIME FROM DUAL UNION ALL
SELECT TO_DATE('20170613 2300', 'YYYYMMDD HH24MI') BTIME, TO_DATE('20170614 0000', 'YYYYMMDD HH24MI') ETIME FROM DUAL UNION ALL
SELECT TO_DATE('20170614 1000', 'YYYYMMDD HH24MI') BTIME, TO_DATE('20170614 1100', 'YYYYMMDD HH24MI') ETIME FROM DUAL UNION ALL
SELECT TO_DATE('20170614 1200', 'YYYYMMDD HH24MI') BTIME, TO_DATE('20170614 1200', 'YYYYMMDD HH24MI') ETIME FROM DUAL
)
SELECT (TO_DATE(:FTIME, 'YYYYMMDD HH24MI') - TO_DATE(:STIME, 'YYYYMMDD HH24MI')) * 60 * 24 TOTAL_WORK
, TOTAL_REST
FROM (SELECT SUM((ETIME - BTIME) * 60 * 24) TOTAL_REST
FROM (SELECT BTIME
, CASE
WHEN ETIME >= TO_DATE(:FTIME, 'YYYYMMDD HH24MI') THEN TO_DATE (:FTIME, 'YYYYMMDD HH24MI')
WHEN TRUNC(ETIME) - TRUNC(BTIME) >= 1 THEN TRUNC(ETIME)
ELSE ETIME
END ETIME
FROM T
WHERE BTIME BETWEEN TO_DATE(:STIME, 'YYYYMMDD HH24MI')
AND TO_DATE(:FTIME, 'YYYYMMDD HH24MI')));
프로시저나 함수 말고 쿼리로도 가능합니다.
stime : 입력 시작시간 (yyyymmdd hh24mi)
ftime : 입력 종료시간 (yyyymmdd hh24mi)
btime : 휴식 시작시간 (yyyymmdd hh24mi)
etime : 휴식 종료시간 (yyyymmdd hh24mi)
WITH t AS
(
SELECT '0000' stm, '0100' etm FROM dual
UNION ALL SELECT '1200', '1300' FROM dual
)
SELECT SUM(a.e - a.s - NVL(SUM(LEAST(a.e, b.e) - GREATEST(a.s, b.s)), 0)) mi
FROM (SELECT LEVEL lv
, DECODE(LEVEL, 1, TO_CHAR(sdt, 'sssss') / 60, 0) s
, DECODE(LEVEL, TRUNC(edt) - TRUNC(sdt) + 1, TO_CHAR(edt, 'sssss') / 60, 24*60) e
FROM (SELECT TO_DATE('2017-06-12 11:00', 'yyyy-mm-dd hh24:mi') sdt
, TO_DATE('2017-06-14 11:00', 'yyyy-mm-dd hh24:mi') edt
FROM dual
)
CONNECT BY LEVEL <= TRUNC(edt) - TRUNC(sdt) + 1
) a
, (SELECT SUBSTR(stm, 1, 2) * 60 + SUBSTR(stm, 3) s
, SUBSTR(etm, 1, 2) * 60 + SUBSTR(etm, 3) e
FROM t
) b
WHERE a.s < b.e(+)
AND a.e > b.s(+)
GROUP BY a.lv, a.s, a.e
;
WITH T_WORK AS (
SELECT TO_DATE('20170612 1100', 'YYYYMMDD HH24MI') BTIME, TO_DATE('20170613 0000', 'YYYYMMDD HH24MI') ETIME FROM DUAL UNION ALL
SELECT TO_DATE('20170613 0000', 'YYYYMMDD HH24MI') BTIME, TO_DATE('20170614 0000', 'YYYYMMDD HH24MI') ETIME FROM DUAL UNION ALL
SELECT TO_DATE('20170614 0900', 'YYYYMMDD HH24MI') BTIME, TO_DATE('20170614 1900', 'YYYYMMDD HH24MI') ETIME FROM DUAL
)
, T_REST AS (
SELECT TO_DATE('20170612 1700', 'YYYYMMDD HH24MI') BTIME, TO_DATE('20170612 180', 'YYYYMMDD HH24MI') ETIME FROM DUAL UNION ALL
SELECT TO_DATE('20170613 1200', 'YYYYMMDD HH24MI') BTIME, TO_DATE('20170613 1300', 'YYYYMMDD HH24MI') ETIME FROM DUAL UNION ALL
SELECT TO_DATE('20170613 2300', 'YYYYMMDD HH24MI') BTIME, TO_DATE('20170614 0000', 'YYYYMMDD HH24MI') ETIME FROM DUAL UNION ALL
SELECT TO_DATE('20170614 1200', 'YYYYMMDD HH24MI') BTIME, TO_DATE('20170614 1300', 'YYYYMMDD HH24MI') ETIME FROM DUAL
)
SELECT TRUNC(A.BTIME) AS WORK_DATE
--, NVL(SUM(A.ETIME-A.BTIME),0)
, (NVL(SUM(A.ETIME-A.BTIME),0)-MIN(DIFF))*24 AS WORK_HOURX
, (NVL(SUM(A.ETIME-A.BTIME),0)-MIN(DIFF))*24 AS WORK_HOUR
, MIN(DIFF)*60*24 AS REST_MIN
FROM T_WORK A
, ( SELECT TRUNC(BTIME) WORK_DATE, NVL(SUM(ETIME-BTIME),0) AS DIFF
FROM T_REST
GROUP BY TRUNC(BTIME)
) B
WHERE TRUNC(A.BTIME) = B.WORK_DATE(+)
GROUP BY TRUNC(A.BTIME)
답변주신 모든 분들 감사합니다..
잘 해결될거 같습니다..
) 버튼을 클릭하여 작성 하시면 됩니다.