Getting available date intervals with overlapping busy times

user_16803239 0
Viewed 27

I'm trying to get available date intervals from a predefined available dates and busy times.

Table of possible dates (slots)

ID SLOT_BEG SLOT_END
1 11/10/2021 09:00:00 11/10/2021 09:15:00
2 11/10/2021 09:15:00 11/10/2021 09:30:00
3 11/10/2021 09:30:00 11/10/2021 09:45:00
4 11/10/2021 09:45:00 11/10/2021 10:00:00

Busy times

ID MEET_BEG MEET_END
1 11/10/2021 09:00:00 11/10/2021 09:15:00
2 11/10/2021 09:32:24 11/10/2021 09:46:00

Desired result:

ID SLOT_BEG SLOT_END
2 11/10/2021 09:15:00 11/10/2021 09:30:00

db<>fiddle here

I'm unable to find a way to handle the overlapping cases like meet ID 2 - it needs to block 2 slots because the starting date is not aligned.

Any help is appreciated.

datetime-conversion oracle sql
1 Answers

Use not exists with a subquery testing if the invervals do overlap

select * from T_TMP_SLOT s
where not exists (
  select null from T_TMP_MEET m
  where  not (s.SLOT_END <= m.MEET_BEG or m.MEET_END <= s.SLOT_BEG)
  );

        ID SLOT_BEG            SLOT_END           
---------- ------------------- -------------------
         2 11.10.2021 09:15:00 11.10.2021 09:30:00

Note that the intervals do not overlap if the slot end before (or equal) begin of meeting OR if the meeting ends before (or eq) slot begin.

So make a negation of this predicate to get overlapping meetings and check with not exists that such meetings do not exists..

user_48081220
Related