데이터베이스 질문과 답변
예전 마농님이 작성해주신 쿼리입니다.
여기서 휴계시간(with절)이 23:00 ~ 01:00 일경우에는 해당시간을 제외 해야하는데 시간 계산이 제대로 되지 않는거 같습니다.
제대로 계산이 되도록 도움 부탁드립니다.
감사합니다.
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 exc_t AS
(
--SELECT '0000' stm, '0100' etm FROM dual
SELECT '2300' stm, '0100' etm FROM dual
UNION ALL SELECT '1200', '1300' FROM dual
)
, data_t AS
(
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
)
SELECT SUM(a.e - a.s - NVL(SUM(LEAST(a.e, b.e) - GREATEST(a.s, b.s)), 0)) mi
FROM (SELECT lv
, DECODE(lv, 1, TO_CHAR(sdt, 'sssss') / 60, 0) s
, DECODE(lv, TRUNC(edt) - TRUNC(sdt) + 1, TO_CHAR(edt, 'sssss') / 60, 24*60) e
FROM data_t
, (SELECT LEVEL lv FROM dual CONNECT BY LEVEL <= 9)
WHERE lv <= TRUNC(edt) - TRUNC(sdt) + 1
) a
, (SELECT CASE WHEN stm > etm AND lv = 2 THEN 0 ELSE SUBSTR(stm, 1, 2) * 60 + SUBSTR(stm, 3) END s
, CASE WHEN stm > etm AND lv = 1 THEN 24*60 ELSE SUBSTR(etm, 1, 2) * 60 + SUBSTR(etm, 3) END e
FROM exc_t
, (SELECT LEVEL lv FROM dual CONNECT BY LEVEL <= 2)
WHERE lv <= CASE WHEN stm > etm THEN 2 ELSE 1 END
) b
WHERE a.s < b.e(+)
AND a.e > b.s(+)
GROUP BY a.lv, a.s, a.e
;
(2300 ~ 0100) 형태를 두개로 쪼개 (2300 ~ 2400), (0000 ~ 0100) 형태로 바꾸는 방법입니다.
다만, 애초에 제외테이블 자체를 이와 같이 쪼개어 관리한다면?
기존 쿼리로도 같은 결과를 얻을 수 있겠네요.
마농님 정말로 감사합니다.
즐거운 하루 보내세요~
) 버튼을 클릭하여 작성 하시면 됩니다.