오라클 특정 일자 중복체크 관련 질문입니다. 1 4 692

by 미어캣 오라클 [2020.09.17 14:45:55]


안녕하세요 휴가 관련해서 중복체크를 할 일이 생겨서 질문드립니다!

아래와 같은 휴가 테이블이 있는데 

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 휴가 등록을 했을때 
중복인 것을 확인하고 싶은데 중복 체크가 가능할까요?

 

by 마농 [2020.09.17 18:08:50]

시간은 항상 09, 14, 18 세가지만 존재하나요?
반차만 존재하나요? 시간휴가(1~7시간)는 없는지?


by 미어캣 [2020.09.18 09:10:57]

시간은 정상근무시간 09시부터 18시까지라서 그 사이 시간은 전부 포함됩니다.

여기는 반차랑 반반차도 있는데 특이케이스로 1시간 단위로 쓸수 있는 사람도 있더라구요.


by 마농 [2020.09.18 14:14:14]
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
;

 


by 미어캣 [2020.09.21 09:23:55]

공부할게 참 많네요. 답변 감사합니다 ^^

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