SELECT SUM( CASE WHEN LUNCH_YN = '1' THEN TO_NUMBER(SUBSTR(A.GUNRO_TIME_FROM,1,2)) - TO_NUMBER(SUBSTR(A.GUNRO_TIME_TO,1,2)) ELSE ( TO_NUMBER((CASE WHEN TO_NUMBER(SUBSTR(A.GUNRO_TIME_FROM,1,2)) > 12 THEN (CASE WHEN TO_NUMBER(SUBSTR(A.GUNRO_TIME_TO,1,2)) >= 13 THEN TO_NUMBER(SUBSTR(A.GUNRO_TIME_FROM,1,2)) ELSE (TO_NUMBER(SUBSTR(A.GUNRO_TIME_FROM,1,2)) - 1) END) ELSE TO_NUMBER(SUBSTR(A.GUNRO_TIME_FROM,1,2)) END)) - TO_NUMBER(SUBSTR(A.GUNRO_TIME_TO,1,2)) ) END ) AS GUNRO_TIME_LUNCH
FROM에 1800이라는 값이 있고 TO에 0900 라는 값이잇으면 앞에서 2자리 짤라서 18 - 9 로 하면 9시간으로 시급을
계산하는 프로세스입니다 . 저기에 점심포함이면 12:00~ 13:00까지 포함시키고 아니면 제외 시키는데요.
여기서 갑작스럽게 30분단위가 추가가 되어 계산하기가 좀 어렵습니다 .
만약에 1800 - 0830 이라는 값이나오면 저는 9시간 30분을 리턴받아야 하는데
계산을 어떻게 해야할지 ... 머리가 안돌아가네요
SELECT SUM(CASE WHEN LUNCH_YN = '1' THEN TO_NUMBER(SUBSTR(A.GUNRO_TIME_FROM, 1, 3)) - TO_NUMBER(SUBSTR(A.GUNRO_TIME_TO, 1, 3)) ELSE (TO_NUMBER((CASE WHEN TO_NUMBER(SUBSTR(A.GUNRO_TIME_FROM, 1, 3)) > 120 THEN (CASE WHEN TO_NUMBER(SUBSTR(A.GUNRO_TIME_TO, 1, 3)) >= 130 THEN TO_NUMBER(SUBSTR(A.GUNRO_TIME_FROM, 1, 3)) ELSE (TO_NUMBER(SUBSTR(A.GUNRO_TIME_FROM, 1, 3)) - 10) END) ELSE TO_NUMBER(SUBSTR(A.GUNRO_TIME_FROM, 1, 3)) END)) - TO_NUMBER(SUBSTR(A.GUNRO_TIME_TO, 1, 3))) END) AS GUNRO_TIME_LUNCH
보통 to 에서 from 을 빼는게 일반적인데.. 여긴 특이하게 거꾸로 되어 있네요?
lunch_yn 에 (1/0) 이 들어온다고 가정합니다.
WITH t (lunch_yn, gunro_time_to, gunro_time_from) AS ( SELECT 0, '0830', '1100' FROM dual UNION ALL SELECT 1, '1130', '1230' FROM dual UNION ALL SELECT 0, '1230', '1400' FROM dual UNION ALL SELECT 0, '0830', '1800' FROM dual UNION ALL SELECT 1, '0830', '1800' FROM dual ) SELECT lunch_yn, gunro_time_to, gunro_time_from , TO_CHAR(TO_DATE( ROUND(((f1 - t1) + (f2 - t2) * lunch_yn + (f3 - t3))*24*60*60) , 'sssss'), 'hh24:mi') tm FROM (SELECT lunch_yn, gunro_time_to, gunro_time_from , TO_DATE(LEAST(gunro_time_to , '1200') , 'hh24mi') t1 , TO_DATE(LEAST(gunro_time_from, '1200') , 'hh24mi') f1 , TO_DATE(LEAST(GREATEST(gunro_time_to , '1200'), '1300'), 'hh24mi') t2 , TO_DATE(LEAST(GREATEST(gunro_time_from, '1200'), '1300'), 'hh24mi') f2 , TO_DATE(GREATEST(gunro_time_to , '1300') , 'hh24mi') t3 , TO_DATE(GREATEST(gunro_time_from, '1300') , 'hh24mi') f3 FROM t ) ;