안녕하세요.
아래 예제와 같은 상황에서 쿼리문을 뽑아야 하는데 어떠한 방식으로
데이터를 뽑아서 계산을 햬야하는지 감이 안잡혀서 질문드립니다.
예시)
시작 시간 - 09:00
종료 시간 - 13:00
중간 체크 시간 - 09:30, 10:20, 12:00
위와 같은 시간 데이터가 있을 때
09:00 에서 09:30 사이에 시간을 구해서 1시간 이상인지 체크 1시간이상이라면 +1
그리고 09:30분에서 10:20분 사이에 시간을 구해서 1시간 이상이면 +1
다음 10:20분에서 12:00 사이에 시간을 구해서 1시간 이상이면 +1
마지막으로 12:00에서 13:00 사이에 시간을 구해서 1시간 이상이면 +1을 하고나서
결과물은최종 플러스된합과 총시간(플러스된)을 구하면됩니다.
예제에서는 최종 2가 나오고 시간은 120분이 되겠습니다.
*여기서 주의할건 시작시간과 종료시간은 A라는 테이블에 같은 ROW에 있지만
중간 체크 시간은 B라는 테이블에 데이터가 ROW별로 쌓여있습니다.
궁금한건 시간데이터를 어떤방식으로 뽑아서 정렬시키고 해당 시간을 구해야할지 감이 안잡혀서 질문드립니다.
WITH t_a AS ( SELECT '09:00' stm, '13:00' etm FROM dual ) , t_b AS ( SELECT '09:30' tm FROM dual UNION ALL SELECT '10:20' FROM dual UNION ALL SELECT '12:00' FROM dual ) SELECT COUNT(*) h FROM (SELECT ( TO_DATE(tm, 'hh24:mi') - TO_DATE(LAG(tm) OVER(ORDER BY tm), 'hh24:mi') ) * 24 h FROM (SELECT tm FROM t_a a, t_b b WHERE b.tm BETWEEN a.stm AND a.etm UNION ALL SELECT stm FROM t_a UNION ALL SELECT etm FROM t_a ) ) WHERE h >= 1 ;
WITH T1 AS ( SELECT '09:00' STIME, '13:00' ETIME FROM DUAL ) , T2 AS (SELECT '09:30' CHKTM FROM DUAL UNION ALL SELECT '10:20' FROM DUAL UNION ALL SELECT '12:00' FROM DUAL ) SELECT SUM(CASE WHEN LEAST(TO_DATE(CHKTM,'HH24:MI'), TO_DATE(ETIME,'HH24:MI')) - GREATEST(TO_DATE(NVL(CHKFM, STIME),'HH24:MI'), TO_DATE(STIME,'HH24:MI')) >= 1/24 THEN 1 ELSE 0 END) + MAX(CASE WHEN TO_DATE(ETIME,'HH24:MI') - TO_DATE(LASTTM,'HH24:MI') >= 1/24 THEN 1 ELSE 0 END)TM2 FROM T1, (SELECT CHKTM , LAG(CHKTM) OVER (ORDER BY CHKTM) CHKFM , MAX(CHKTM) OVER() LASTTM FROM T2)