I am trying to split work shift date range into different date and time interval. Already found some answers, but still trying to split by time interval. Thanks in advance for any ideas or tips.
- Each day need to be split out separately
- Day Shift is 0600-22:00
- Night Shift is 2200-0600
Range1:
2022-02-03 08:40 to 2022-02-04 10:07
Split Rows:
2022-02-03 08:40 to 2022-02-03 22:00 DAY
2022-02-03 22:00 to 2022-02-04 06:00 NIGHT
2022-02-04 06:00 to 2022-02-04 10:07 DAY
Range2:
2022-02-03 08:40 to 2022-02-04 02:07
Split Rows:
2022-02-03 08:40 to 2022-02-03 22:00 DAY
2022-02-03 22:00 to 2022-02-04 02:07 NIGHT
Range3:
2022-02-03 04:40 to 2022-02-04 02:07
Split Rows:
2022-02-03 04:40 to 2022-02-03 06:00 NIGHT
2022-02-03 08:40 to 2022-02-03 22:00 DAY
2022-02-03 22:00 to 2022-02-04 02:07 NIGHT
Sample data (Using lateral query is not working yet. I will update, if i figure it out) Also trying to see whether i can split them per hour and sum up later as in here splitting time into hour intervals
WITH SAMPLE AS (
SELECT
1 AS ID,
TO_DATE('2022-02-03 08:40', 'YYYY-MM-DD HH24:MI') AS STARTDATE,
TO_DATE('2022-02-04 10:07', 'YYYY-MM-DD HH24:MI') AS ENDDATE
FROM
DUAL
UNION ALL
SELECT
2 AS ID,
TO_DATE('2022-02-03 08:40', 'YYYY-MM-DD HH24:MI') AS STARTDATE,
TO_DATE('2022-02-04 02:07', 'YYYY-MM-DD HH24:MI') AS ENDDATE
FROM
DUAL
UNION ALL
SELECT
3 AS ID,
TO_DATE('2022-02-03 04:40', 'YYYY-MM-DD HH24:MI') AS STARTDATE,
TO_DATE('2022-02-04 02:07', 'YYYY-MM-DD HH24:MI') AS ENDDATE
FROM
DUAL
)
SELECT
ID,
L.STARTDATE,
L.ENDDATE
FROM
SAMPLE,
LATERAL (
SELECT
CASE LEVEL
WHEN 1 THEN STARTDATE
ELSE TRUNC(STARTDATE) LEVEL - 1
END STARTDATE,
LEAST(TRUNC(STARTDATE) LEVEL - 1 / 24 / 60, ENDDATE) ENDDATE
FROM
DUAL
CONNECT BY
TRUNC(STARTDATE) LEVEL - 1 <= ENDDATE
) L;
CodePudding user response：
You can use:
WITH SAMPLE (ID, startdate, enddate ) AS (
SELECT 1,
TO_DATE('2022-02-03 08:40', 'YYYY-MM-DD HH24:MI'),
TO_DATE('2022-02-04 10:07', 'YYYY-MM-DD HH24:MI')
FROM DUAL
UNION ALL
SELECT 2,
TO_DATE('2022-02-03 08:40', 'YYYY-MM-DD HH24:MI'),
TO_DATE('2022-02-04 02:07', 'YYYY-MM-DD HH24:MI')
FROM DUAL
UNION ALL
SELECT 3,
TO_DATE('2022-02-03 04:40', 'YYYY-MM-DD HH24:MI'),
TO_DATE('2022-02-04 02:07', 'YYYY-MM-DD HH24:MI')
FROM DUAL
)
SELECT ID,
o.type,
GREATEST(L.start_date o.start_offset, s.startdate) AS startdate,
LEAST(L.start_date o.end_offset, s.enddate) AS enddate
FROM SAMPLE s
CROSS JOIN LATERAL (
SELECT TRUNC(startdate - INTERVAL '6' HOUR)
INTERVAL '6' HOUR
LEVEL - 1 AS start_date
FROM DUAL
CONNECT BY
TRUNC(startdate - INTERVAL '6' HOUR)
INTERVAL '6' HOUR
LEVEL - 1
< ENDDATE
) L
CROSS JOIN (
SELECT 'DAY' AS type,
INTERVAL '0' HOUR AS start_offset,
INTERVAL '16' HOUR AS end_offset
FROM DUAL
UNION ALL
SELECT 'NIGHT' AS type,
INTERVAL '16' HOUR AS start_offset,
INTERVAL '24' HOUR AS end_offset
FROM DUAL
) o
WHERE L.start_date o.start_offset < s.enddate
AND L.start_date o.end_offset > s.startdate;
Which outputs:
ID TYPE STARTDATE ENDDATE 1 DAY 2022-02-03 08:40:00 2022-02-03 22:00:00 1 NIGHT 2022-02-03 22:00:00 2022-02-04 06:00:00 1 DAY 2022-02-04 06:00:00 2022-02-04 10:07:00 2 DAY 2022-02-03 08:40:00 2022-02-03 22:00:00 2 NIGHT 2022-02-03 22:00:00 2022-02-04 02:07:00 3 NIGHT 2022-02-03 04:40:00 2022-02-03 06:00:00 3 DAY 2022-02-03 06:00:00 2022-02-03 22:00:00 3 NIGHT 2022-02-03 22:00:00 2022-02-04 02:07:00
db<>fiddle here
CodePudding user response：
Step 1.
First of all you need to generate all possible intervals. You can do it using simple lateral. To make it easier and more agile, I'll save day shifts in the INTERVALS CTE:
WITH SAMPLE AS (
SELECT
1 AS ID,
TO_DATE('2022-02-03 08:40', 'YYYY-MM-DD HH24:MI') AS STARTDATE,
TO_DATE('2022-02-04 10:07', 'YYYY-MM-DD HH24:MI') AS ENDDATE
FROM
DUAL
UNION ALL
SELECT
2 AS ID,
TO_DATE('2022-02-03 08:40', 'YYYY-MM-DD HH24:MI') AS STARTDATE,
TO_DATE('2022-02-04 02:07', 'YYYY-MM-DD HH24:MI') AS ENDDATE
FROM
DUAL
UNION ALL
SELECT
3 AS ID,
TO_DATE('2022-02-03 04:40', 'YYYY-MM-DD HH24:MI') AS STARTDATE,
TO_DATE('2022-02-04 02:07', 'YYYY-MM-DD HH24:MI') AS ENDDATE
FROM
DUAL
)
,intervals(i_name,i_start,i_end) as (
select 'Day Shift' ,'0600', '2159' from dual union all
select 'Night Shift','2200', '0559' from dual
)
SELECT
s.*
,days.*
,ints.*
FROM
SAMPLE s
,lateral(
select trunc(startdate) n as n_day
from xmltable(
'-1 to xs:integer(.)'
passing trunc(trunc(enddate) - trunc(startdate))
columns n int path '.'
)
) days
,lateral(
select
i.*
,to_date(to_char(n_day,'yyyy-mm-dd ')||i_start, 'yyyy-mm-dd hh24mi')
as dtm_start
,to_date(to_char(n_day,'yyyy-mm-dd ')||i_end , 'yyyy-mm-dd hh24mi')
case when i_end < i_start then 1 else 0 end -- 1 if it ends on next day
as dtm_end
from intervals i
) ints
order by id,startdate,n_day,dtm_start;
Results:
ID STARTDATE ENDDATE N_DAY I_NAME I_ST I_EN DTM_START DTM_END
--- ------------------- ------------------- ---------- ----------- ---- ---- ------------------- -------------------
1 2022-02-03 08:40:00 2022-02-04 10:07:00 2022-02-02 Day Shift 0600 2159 2022-02-02 06:00:00 2022-02-02 21:59:00
1 2022-02-03 08:40:00 2022-02-04 10:07:00 2022-02-02 Night Shift 2200 0559 2022-02-02 22:00:00 2022-02-03 05:59:00
1 2022-02-03 08:40:00 2022-02-04 10:07:00 2022-02-03 Day Shift 0600 2159 2022-02-03 06:00:00 2022-02-03 21:59:00
1 2022-02-03 08:40:00 2022-02-04 10:07:00 2022-02-03 Night Shift 2200 0559 2022-02-03 22:00:00 2022-02-04 05:59:00
1 2022-02-03 08:40:00 2022-02-04 10:07:00 2022-02-04 Day Shift 0600 2159 2022-02-04 06:00:00 2022-02-04 21:59:00
1 2022-02-03 08:40:00 2022-02-04 10:07:00 2022-02-04 Night Shift 2200 0559 2022-02-04 22:00:00 2022-02-05 05:59:00
2 2022-02-03 08:40:00 2022-02-04 02:07:00 2022-02-02 Day Shift 0600 2159 2022-02-02 06:00:00 2022-02-02 21:59:00
2 2022-02-03 08:40:00 2022-02-04 02:07:00 2022-02-02 Night Shift 2200 0559 2022-02-02 22:00:00 2022-02-03 05:59:00
2 2022-02-03 08:40:00 2022-02-04 02:07:00 2022-02-03 Day Shift 0600 2159 2022-02-03 06:00:00 2022-02-03 21:59:00
2 2022-02-03 08:40:00 2022-02-04 02:07:00 2022-02-03 Night Shift 2200 0559 2022-02-03 22:00:00 2022-02-04 05:59:00
2 2022-02-03 08:40:00 2022-02-04 02:07:00 2022-02-04 Day Shift 0600 2159 2022-02-04 06:00:00 2022-02-04 21:59:00
2 2022-02-03 08:40:00 2022-02-04 02:07:00 2022-02-04 Night Shift 2200 0559 2022-02-04 22:00:00 2022-02-05 05:59:00
3 2022-02-03 04:40:00 2022-02-04 02:07:00 2022-02-02 Day Shift 0600 2159 2022-02-02 06:00:00 2022-02-02 21:59:00
3 2022-02-03 04:40:00 2022-02-04 02:07:00 2022-02-02 Night Shift 2200 0559 2022-02-02 22:00:00 2022-02-03 05:59:00
3 2022-02-03 04:40:00 2022-02-04 02:07:00 2022-02-03 Day Shift 0600 2159 2022-02-03 06:00:00 2022-02-03 21:59:00
3 2022-02-03 04:40:00 2022-02-04 02:07:00 2022-02-03 Night Shift 2200 0559 2022-02-03 22:00:00 2022-02-04 05:59:00
3 2022-02-03 04:40:00 2022-02-04 02:07:00 2022-02-04 Day Shift 0600 2159 2022-02-04 06:00:00 2022-02-04 21:59:00
3 2022-02-03 04:40:00 2022-02-04 02:07:00 2022-02-04 Night Shift 2200 0559 2022-02-04 22:00:00 2022-02-05 05:59:00
Note, that since I have specified time intervals in hhmi (ie hh24mi in oracle datetime format models), we need to ignore seconds.
As you can see lateral(...) days generates all dates between one day before startdate (to cover the end of night shift) and enddate.
Then ints generates day and night shifts for all those days.
Step 2.
So the only thing you need now is to filter them and correct start time and end time of partial intervals.
These 2 predicates filters them:
and ints.dtm_end >= s.startdate
and ints.dtm_start <= s.enddate
and these 2 lines return correct start and end time:
greatest(s.startdate, ints.dtm_start) as startdate,
least (s.enddate , ints.dtm_end ) as enddate,
So full solution: DBFiddle
WITH SAMPLE AS (
SELECT
1 AS ID,
TO_DATE('2022-02-03 08:40', 'YYYY-MM-DD HH24:MI') AS STARTDATE,
TO_DATE('2022-02-04 10:07', 'YYYY-MM-DD HH24:MI') AS ENDDATE
FROM
DUAL
UNION ALL
SELECT
2 AS ID,
TO_DATE('2022-02-03 08:40', 'YYYY-MM-DD HH24:MI') AS STARTDATE,
TO_DATE('2022-02-04 02:07', 'YYYY-MM-DD HH24:MI') AS ENDDATE
FROM
DUAL
UNION ALL
SELECT
3 AS ID,
TO_DATE('2022-02-03 04:40', 'YYYY-MM-DD HH24:MI') AS STARTDATE,
TO_DATE('2022-02-04 02:07', 'YYYY-MM-DD HH24:MI') AS ENDDATE
FROM
DUAL
)
,intervals(i_name,i_start,i_end) as (
select 'Day Shift' ,'0600', '2159' from dual union all
select 'Night Shift','2200', '0559' from dual
)
SELECT
s.id,
greatest(s.startdate, ints.dtm_start) as startdate,
least (s.enddate , ints.dtm_end ) as enddate,
i_name,
i_start,
i_end
FROM
SAMPLE s
,lateral(
select trunc(startdate) n as n_day
from xmltable(
'-1 to xs:integer(.)'
passing trunc(trunc(enddate) - trunc(startdate))
columns n int path '.'
)
) days
,lateral(
select
i.*
,to_date(to_char(n_day,'yyyy-mm-dd ')||i_start, 'yyyy-mm-dd hh24mi')
as dtm_start
,to_date(to_char(n_day,'yyyy-mm-dd ')||i_end , 'yyyy-mm-dd hh24mi')
case when i_end < i_start then 1 else 0 end -- 1 if it ends on next day
as dtm_end
from intervals i
) ints
where 1=1
-- filter `ints`:
and ints.dtm_end >= s.startdate
and ints.dtm_start <= s.enddate
order by 1,2,3;
Results:
ID STARTDATE ENDDATE I_NAME I_ST I_EN
---------- ---------------- ---------------- ----------- ---- ----
1 2022-02-03 08:40 2022-02-03 21:59 Day Shift 0600 2159
1 2022-02-03 22:00 2022-02-04 05:59 Night Shift 2200 0559
1 2022-02-04 06:00 2022-02-04 10:07 Day Shift 0600 2159
2 2022-02-03 08:40 2022-02-03 21:59 Day Shift 0600 2159
2 2022-02-03 22:00 2022-02-04 02:07 Night Shift 2200 0559
3 2022-02-03 04:40 2022-02-03 05:59 Night Shift 2200 0559
3 2022-02-03 06:00 2022-02-03 21:59 Day Shift 0600 2159
3 2022-02-03 22:00 2022-02-04 02:07 Night Shift 2200 0559
8 rows selected.
Obviously, you can remove i_start and i_end columns from the output. I showed them just to highlight day/night shift intervals.