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.

CodePudding user response：

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..