The following SQL would return rows with employee schedule when they are working (WTYPE='W') and when they are Out of Office (OOO) (WTYPE='O'). I'm looking for a way to adjust the work schedule for an employee when there is an OOO, i.e., display the work schedule minus the OOO time range.
So, for the following example the final SQL would return 2 rows with work schedule from 08:30 - 10:00 and 12:30 - 17:30. In cases when the OOO is in the beginning or towards the end we'll see only 1 row for the work shift.
select 1 emp_id, 'Kashif Khan' emp_name, to_date('17-JUL-2020 08:30','DD-MON-YYYY HH24:MI') sdt, to_date('17-JUL-2020 17:30','DD-MON-YYYY HH24:MI') edt, 'W' wtype from dual
union
select 1 emp_id, 'Kashif Khan' emp_name, to_date('17-JUL-2020 10:00','DD-MON-YYYY HH24:MI') sdt, to_date('17-JUL-2020 12:30','DD-MON-YYYY HH24:MI') edt, 'O' wtype from dual;
EMP_ID EMP_NAME SDT EDT W
---------- ----------- ----------------------- ----------------------- -
1 Kashif Khan 17-JUL-2020 08:30:00 AM 17-JUL-2020 05:30:00 PM W
1 Kashif Khan 17-JUL-2020 10:00:00 AM 17-JUL-2020 12:30:00 PM O
Any idea how can that be achieved without making it overly complex?
Thanks in advance.