법정근무시간 1일8시간
휴게시간 4시간당 30분
법정근무시간 이후시간은 연장시간
야간근로 22시 ~ 익일6시까지
이런조건이 있는데요 이런조건으로 근무시간,연장시간,야간근로시간을 계산하는걸 만드려고하는데요
ex)
13:00 ~ 22:00 까지근무하면 원래는 총근무시간이9시간인데 4시간마다 30분씩 휴게시간이 생기므로 근무시간만 8시간이되고요
16:00~23:00 까지근무하면 총근무시간은 6시간인데 4시간만다 30분 휴게시간이 생기므로 근무시간이 5.5 가되면 22시이후 근무가 1시간생기므로 야간근로시간 1시간이 되고요
10:00 ~ 22:00 까지 근무이면 근무시간 8시간에 연장시간 1시간
이런식으로 계산이 되는걸 구현하려고 합니다.
WITH t AS
(
SELECT 1 empno, '20170720' dt, '14' s_hh, '00' s_mi, '23' e_hh, '00' e_mi FROM dual
UNION ALL SELECT 2, '20170720', '12', '00', '22', '00' FROM dual
UNION ALL SELECT 3, '20170720', '09', '30', '16', '00' FROM dual
UNION ALL SELECT 4, '20170720', '10', '00', '18', '00' FROM dual
UNION ALL SELECT 5, '20170720', '09', '30', '16', '30' FROM dual
UNION ALL SELECT 6, '20170720', '09', '30', '17', '00' FROM dual
UNION ALL SELECT 7, '20170720', '09', '30', '14', '30' FROM dual
UNION ALL SELECT 8, '20170720', '10', '00', '17', '00' FROM dual
UNION ALL SELECT 9, '20170720', '16', '30', '23', '00' FROM dual
UNION ALL SELECT 10, '20170720', '09', '30', '14', '30' FROM dual
UNION ALL SELECT 11, '20170720', '22', '00', '07', '00' FROM dual
)
SELECT empno, dt
, s_hh, s_mi, e_hh, e_mi
, SUM(DECODE(gb, 1, work_tm - night_tm )) 근무시간
, SUM(DECODE(gb, 2, work_tm - night_tm, 0)) 연장시간
, SUM(night_tm) 야간시간
FROM (SELECT empno, dt
, s_hh, s_mi, e_hh, e_mi
, CASE WHEN lv < 3 THEN 1 ELSE 2 END gb
, s, e
, (e - s) * 24 AS work_tm
, GREATEST(0, (LEAST(e, TRUNC(s)+30/24) - GREATEST(s, TRUNC(s)+22/24)) * 24)
+ GREATEST(0, (LEAST(e, TRUNC(s)+ 6/24) - GREATEST(s, TRUNC(s)- 2/24)) * 24)
AS night_tm
FROM (SELECT empno, dt
, s_hh, s_mi, e_hh, e_mi
, lv
, s + 4.5/24 * (lv - 1) s
, LEAST(s + 4.5/24 * lv - 0.5/24, e) e
FROM (SELECT empno, dt
, s_hh, s_mi, e_hh, e_mi
, TO_DATE(s_hh||s_mi, 'hh24mi') s
, TO_DATE(e_hh||e_mi, 'hh24mi')
+ CASE WHEN s_hh||s_mi > e_hh||e_mi THEN 1 ELSE 0 END e
FROM t
)
, (SELECT LEVEL lv FROM dual CONNECT BY LEVEL < 9)
WHERE lv <= CEIL((e - s)*24/4.5)
)
)
GROUP BY empno, dt, s_hh, s_mi, e_hh, e_mi
ORDER BY empno, dt
해서 이렇게 SQL을 만들어 주셔서 도움을 받았는데요
한가지더 조건이 있는데요
근무시간,연장시간,야간시간 구분해서 휴게시간이 4시간에 30분이라는 조건이 더 추가되서 도움요청드립니다.
위에 SQL에서 다른건 다잘 나오는데요
empno가 1번 직원은 근무시간 7.5 / 연장 0 / 야간 0.5가 나오는데요 --> 근무시간 7 / 연장 0 / 야간 1 이렇게 나와야 되며
empno가 9번 직원은 근무시간 5 / 연장 0 / 야간 1가 나오는데요 --> 근무시간 5.5 / 연장 0 / 야간 1 이렇게 나와야 되야 합니다.
법정근무에서 연장근무로 넘어가는 경우
- 4.0근무 > 0.5휴식 > 4.0근무 > 0.5휴식 > 연장근무시작
근무중 야간근무로 넘어가는 경우
- 4시간 이하 근무 도중 21:30분이 되면 > (21:30~22:00 30분 무조건 휴식) > 22시 야간근무 시작
이런 경우는?
- 4시간 근무 후 30분을 휴식했는데. 근무 시작하려는 시간이 공교롭게도 21:30 이면?
- 30분 더 쉬고 22시부터 야간 들어갈지?
- (21:30~22:00) 30분 일반근무하고, 바로 쉬지 않고 22시 야간 돌입할지?
- 이 경우 4시간 책정을 21:30 부터 시작할지?
- 아니면 야간 시작인 22:00 부터 새로 시작할지?