시작시간(start)과 종료시간(end)의 실제 소요시간을 계산하고 싶습니다.
전제조건은 아래와 같습니다.
1. 평일 근무시간은 06시 ~ 22시 까지이다.
2. 주말은 금요일 22시 부터 월요일 06시 까지다
실제 소요시간을 계산해라.
Ex.
평일 오후 6시에 시작됐고 23시에 종료 됐을 경우 소요시간은 4시간임.
금요일 오후 22시에 시작됐고 월요일 오전 6시에 종료 됐을 경우 소요시간은 0시간 0분임
월요일 오후 6시에 시작됐고 화요일 오전 9시에 종료 됐을 경우 소요시간은 7시간임
월요일 오후 6시에 시작됐고 월요일 오후 11시에 종료 됐을 경우 소요시간은 4시간임
프로그램으로 만들고 있는데 쿼리로 될까해서 질문올려봅니다...
WITH t AS ( SELECT 1 idx, '2020.02.04 18:00:00' sdt, '2020.02.04 23:00:00' edt UNION ALL SELECT 2, '2020.02.07 22:00:00', '2020.02.10 06:00:00' UNION ALL SELECT 3, '2020.02.10 18:00:00', '2020.02.11 09:00:00' UNION ALL SELECT 4, '2020.02.10 18:00:00', '2020.02.10 23:00:00' UNION ALL SELECT 5, '2020.02.10 17:30:30', '2020.02.13 10:10:10' ) , t1 AS ( SELECT idx , CAST(sdt AS DATETIME) sdt , CAST(edt AS DATETIME) edt FROM t ) , copy_t AS ( SELECT 0 lv UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 ) SELECT idx , sdt , edt , FLOOR(sec / 60 / 60) hh , MOD(FLOOR(sec / 60), 60) mi , MOD(sec, 60) ss FROM (SELECT idx , sdt , edt , SUM( HOUR (TIMEDIFF(e, s)) * 60 * 60 + MINUTE(TIMEDIFF(e, s)) * 60 + SECOND(TIMEDIFF(e, s)) ) sec FROM (SELECT idx , sdt , edt , GREATEST(sdt, DATE(sdt) + INTERVAL lv DAY + INTERVAL 6 HOUR) s , LEAST (edt, DATE(sdt) + INTERVAL lv DAY + INTERVAL 22 HOUR) e , DAYOFWEEK(DATE(sdt) + INTERVAL lv DAY) dy FROM t1 a INNER JOIN copy_t b ON lv <= DATEDIFF(DATE(edt), DATE(sdt)) ) a WHERE dy NOT IN (1, 7) GROUP BY idx, sdt, edt ) a ;
쿼리에 오류가 있어서 수정합니다.
-- 쿼리에 오류가 있어서 수정합니다. WITH t AS ( SELECT 1 idx, '2020.02.04 18:00:00' sdt, '2020.02.04 23:00:00' edt UNION ALL SELECT 2, '2020.02.07 22:00:00', '2020.02.10 06:00:00' UNION ALL SELECT 3, '2020.02.10 18:00:00', '2020.02.11 09:00:00' UNION ALL SELECT 4, '2020.02.10 18:00:00', '2020.02.10 23:00:00' UNION ALL SELECT 5, '2020.02.10 17:30:30', '2020.02.13 10:10:10' UNION ALL SELECT 6, '2020.02.13 04:00:00', '2020.02.13 05:00:00' -- test 추가 UNION ALL SELECT 7, '2020.02.13 22:30:00', '2020.02.13 23:30:00' -- test 추가 UNION ALL SELECT 8, '2020.02.13 23:00:00', '2020.02.14 05:00:00' -- test 추가 ) , t1 AS ( SELECT idx , CAST(sdt AS DATETIME) sdt , CAST(edt AS DATETIME) edt FROM t ) , copy_t AS ( SELECT 0 lv UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 ) SELECT idx , sdt , edt , FLOOR(sec / 60 / 60) hh , MOD(FLOOR(sec / 60), 60) mi , MOD(sec, 60) ss FROM (SELECT idx , sdt , edt , SUM(CASE WHEN s < e -- 오류 보정 THEN HOUR (TIMEDIFF(e, s)) * 60 * 60 + MINUTE(TIMEDIFF(e, s)) * 60 + SECOND(TIMEDIFF(e, s)) ELSE 0 END ) sec FROM (SELECT idx , sdt , edt , GREATEST(sdt, DATE(sdt) + INTERVAL lv DAY + INTERVAL 6 HOUR) s , LEAST (edt, DATE(sdt) + INTERVAL lv DAY + INTERVAL 22 HOUR) e , DAYOFWEEK(DATE(sdt) + INTERVAL lv DAY) dy FROM t1 a INNER JOIN copy_t b ON lv <= DATEDIFF(DATE(edt), DATE(sdt)) ) a WHERE dy NOT IN (1, 7) GROUP BY idx, sdt, edt ) a ;
답변 감사합니다. 오류 수정까지..
몇가지 궁금한것이 있습니다.
1. 실제 테이블에 적용해서 계산해보니 다르게 나오는건 왜 그런걸까요?
WITH t 에 넣었을 때 문제되는 값이 정상적으로 계산되어지는데
실제 테이블로 여러개의 값에 적용해보면 WITH t 값과 계산된 값이 차이가 좀 많이 나는 경우가 있습니다
2. copy_t 데이터를 9까지 넣으신 이유가 무엇인가요?
3. ON lv <= DATEDIFF(DATE(edt), DATE(sdt)) 값에서 9 이상 값이 나올 경우 다중 행이 나오는데
lv 값을 보면 0 아니면 1이 나옵니다 이값에 의미는 뭔지 알 수 있을까요?
4. WITH copy_t 를 없애려면 어떤 방식을 취하면 될까요?