Oracle find gaps split by allowed slots-CodePudding

I have this table of calendar gaps (for a report):

begindate               enddate 
2017-12-14 16:45:00     2017-12-14 21:45:00
2017-12-15 17:45:00     2017-12-16 10:00:00

The second line range on 2 different days.

I want to split it with given 'allowed' time slots, gaps can only be between 7am and 8pm (20:00:00).

So the result should be 3 lines:

begindate               enddate 
2017-12-14 16:45:00     2017-12-14 20:00:00
2017-12-15 17:45:00     2017-12-15 20:00:00
2017-12-16 07:00:00     2017-12-16 10:00:00

How can I do that in sql (oracle function allowed).

CodePudding user response：

This was an interesting one, here is my answer:

WITH test_data (beginDate, endDate) AS
(
  SELECT TO_DATE('2017-12-14 16:45:00', 'YYYY-MM-DD HH24:MI:SS'),
         TO_DATE('2017-12-14 21:45:00', 'YYYY-MM-DD HH24:MI:SS') FROM DUAL
  UNION ALL
  SELECT TO_DATE('2017-12-15 17:45:00', 'YYYY-MM-DD HH24:MI:SS'),
         TO_DATE('2017-12-16 10:00:00', 'YYYY-MM-DD HH24:MI:SS') FROM DUAL
  UNION ALL
  SELECT TO_DATE('2017-12-15 01:45:00', 'YYYY-MM-DD HH24:MI:SS'),
         TO_DATE('2017-12-15 06:00:00', 'YYYY-MM-DD HH24:MI:SS') FROM DUAL
),
split_dates (beginDate, endDate, actEnd, remaining, lvl) AS
(
  SELECT beginDate, LEAST(endDate, TRUNC(beginDate) 1), endDate, TRUNC(endDate) - TRUNC(beginDate), 1
  FROM test_data
  UNION ALL
  SELECT TRUNC(beginDate) lvl, LEAST(actEnd, TRUNC(beginDate) lvl 1), actEnd, remaining-1, lvl 1
  FROM split_dates sd
  WHERE sd.remaining > 0
)
SELECT TO_CHAR(GREATEST(sd.beginDate, TRUNC(sd.beginDate) 7/24), 'YYYY-MM-DD HH24:MI:SS') AS beginDate,
       TO_CHAR(LEAST(sd.endDate, TRUNC(sd.beginDate) 5/6), 'YYYY-MM-DD HH24:MI:SS') AS endDate
FROM split_dates sd
WHERE GREATEST(sd.beginDate, TRUNC(sd.beginDate) 7/24) <= LEAST(sd.endDate, TRUNC(sd.beginDate) 5/6);

The problem is two-fold:

You need to split multi-day records in separate rows. I accomplished this with the split_records CTE.
You need to overlay your valid times on the calculated splits and check that the new times are valid.

I created a DBFiddle to show you the query in action (Link)