안녕하세요 휴가 관련해서 중복체크를 할 일이 생겨서 질문드립니다! 아래와 같은 휴가 테이블이 있는데 WITH t AS ( SELECT '00001' AS PK, 'admin' AS USER_ID, '2020.09.16' AS START_DATE, '09' AS START_TIME, '00' AS START_MIN, '2020.09.18' AS END_DATE, '18' AS END_TIME, '00' AS END_MIN, '3' AS TOTAL_DAY, '' AS RETURN_ID, '2020.09.01 13:14:01' AS REG_DT FROM DUAL UNION ALL SELECT '00002' AS PK, 'admin' AS USER_ID, '2020.09.16' AS START_DATE, '09' AS START_TIME, '00' AS START_MIN, '2020.09.16' AS END_DATE, '14' AS END_TIME, '00' AS END_MIN, '-0.5' AS TOTAL_DAY, '00001' AS RETURN_ID, '2020.09.01 14:15:21' AS REG_DT FROM DUAL UNION ALL SELECT '00003' AS PK, 'admin' AS USER_ID, '2020.09.16' AS START_DATE, '14' AS START_TIME, '00' AS START_MIN, '2020.09.16' AS END_DATE, '18' AS END_TIME, '00' AS END_MIN, '-0.5' AS TOTAL_DAY, '00001' AS RETURN_ID, '2020.09.01 14:35:12' AS REG_DT FROM DUAL UNION ALL SELECT '00004' AS PK, 'admin' AS USER_ID, '2020.09.15' AS START_DATE, '09' AS START_TIME, '00' AS START_MIN, '2020.09.16' AS END_DATE, '18' AS END_TIME, '00' AS END_MIN, '2' AS TOTAL_DAY, '' AS RETURN_ID, '2020.09.01 16:21:11' AS REG_DT FROM DUAL UNION ALL SELECT '00005' AS PK, 'admin' AS USER_ID, '2020.09.16' AS START_DATE, '14' AS START_TIME, '00' AS START_MIN, '2020.09.16' AS END_DATE, '18' AS END_TIME, '00' AS END_MIN, '-0.5' AS TOTAL_DAY, '00004' AS RETURN_ID, '2020.09.01 16:23:13' AS REG_DT FROM DUAL ) SELECT * FROM t; 1. 2020.09.16 ~ 2020.09.18에 휴가를 신청 2. 2020.09.16을 반차로 각각 휴가 반납 3. 2020.09.15 ~ 2020.09.16에 휴가를 신청 4. 2020.09.16 14:00 ~ 18:00 휴가 반납 이 상태에서 2020.09.16 09:00 ~ 2020.09.16 18:00 휴가 등록을 했을때 중복인 것을 확인하고 싶은데 중복 체크가 가능할까요?
WITH t AS ( SELECT '00001' pk, 'admin' user_id , '2020.09.16' start_date, '09' start_time, '00' start_min , '2020.09.18' end_date, '18' end_time, '00' end_min , 3 total_day FROM dual UNION ALL SELECT '00002', 'admin', '2020.09.16', '09', '00', '2020.09.16', '14', '00', -0.5 FROM dual UNION ALL SELECT '00003', 'admin', '2020.09.16', '14', '00', '2020.09.16', '18', '00', -0.5 FROM dual UNION ALL SELECT '00004', 'admin', '2020.09.15', '09', '00', '2020.09.16', '18', '00', 2.0 FROM dual UNION ALL SELECT '00005', 'admin', '2020.09.16', '14', '00', '2020.09.16', '18', '00', -0.5 FROM dual ) SELECT * FROM (SELECT user_id , tm stm , LEAD(tm) OVER(ORDER BY tm) etm , SUM(SUM(s)) OVER(ORDER BY tm) s FROM (SELECT a.user_id , DECODE(lv, 1, GREATEST(a.stm, b.stm), LEAST(a.etm, b.etm)) tm , DECODE(lv, 1, b.s, -b.s) s FROM (SELECT 'admin' user_id , '2020.09.16 09:00' stm , '2020.09.16 18:00' etm FROM dual ) a LEFT OUTER JOIN (SELECT user_id , start_date ||' '|| start_time ||':'|| start_min stm , end_date ||' '|| end_time ||':'|| end_min etm , SIGN(total_day) s FROM t ) b ON a.user_id = b.user_id AND a.stm < b.etm AND a.etm > b.stm CROSS JOIN (SELECT LEVEL lv FROM dual CONNECT BY LEVEL <= 2) c ) GROUP BY user_id, tm ) WHERE s > 0 ;