쿼리문 질문 드립니다. 0 3 1,359

by yozm0213 [SQL Query] [2016.08.05 15:22:05]


안녕하세요.

아래 예제와 같은 상황에서 쿼리문을 뽑아야 하는데 어떠한 방식으로

데이터를 뽑아서 계산을 햬야하는지 감이 안잡혀서 질문드립니다.

 

예시)

시작 시간 - 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별로 쌓여있습니다.

궁금한건 시간데이터를 어떤방식으로 뽑아서 정렬시키고 해당 시간을 구해야할지 감이 안잡혀서 질문드립니다.

by 마농 [2016.08.05 17:13:44]
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
;

 


by jkson [2016.08.05 17:20:01]
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)

 


by yozm0213 [2016.08.09 10:40:39]

답변 채택이 하나밖에 안되는군요.. 2개의 쿼리문을 응용하여 조회해봤는데 잘되더라구요 감사합니다^^*

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입