Select outside multiple date ranges

Transact-SQL
1

Hi folks!

I have a schedule in which there are time blockages where appointments must not be scheduled, such as follows for example:

ID	BlockStartTime	BlockEndTime
1	2022-06-28 07:00	2022-06-28 20:50
2	2022-06-29 09:00	2022-06-29 09:30
3	2022-06-30 10:20	2022-06-30 11:40

In effect, I am looking to create a query that would do the same as this:

Select * from Schedule where not (
((AppointmentStart Between '2022-06-28 07:00' and '2022-06-28 20:50') and
(AppointmentEnd Between '2022-06-28 07:00' and '2022-06-28 20:50')) or
((AppointmentStart Between '2022-06-29 09:00' and '2022-06-29 09:30') and
(AppointmentEnd Between '2022-06-29 09:00' and '2022-06-29 09:30')) or
((AppointmentStart Between '2022-06-30 10:20' and '2022-06-30 11:40') and
(AppointmentEnd Between '2022-06-30 10:20' and '2022-06-30 11:40')) or ... and so on.

How could I build a query which could do this, enumerating all the blockages? Any insight would be appreciated. Thanks!

2

Something like this?

Select *
  From Schedule       s
 Where Not Exists (
       Select *
         From BlockedSchedule         bs
        Where s.AppointmentStart Between bs.BlockStartTime And bs.BlockEndTime)
2 Likes
3

This works. I hadn't considered your approach of testing for logical existence. In my mind, I thought the only way was through intricate value comparisons which would have been much more complex. Your approach is straightforward. From this point, all I need to do is add

And Not Exists (
       Select *
         From BlockedSchedule         bs
        Where s.AppointmentEnd Between bs.BlockStartTime And bs.BlockEndTime)

Thank you so much for taking the time to read and understand the request. Very very appreciated !!!