SQL Filter date range query

Question

I have a list of schedule records

StaffSchedules

Some of them were already booked.

BookedSchedules

Also there are some holidays

Holidays

I want to get only the available Staff Schedules

Expected Schedules= StaffSchedule - BookedSchedule-Holidays

i.e, I want row 2,6 only from StaffSchedule Table

Below is the query i tried, but it shows no results

with NonBookingSlots as
(
select StartdateTime,EndDateTime from Holidays
union all
select StartdateTime,EndDateTime from BookedSchedules
)

SELECT
    StaffId, StartdateTime, EndDateTime
FROM StaffSchedule
WHERE 
not exists (select 1
                from NonBookingSlots h
                where cast(StartdateTime as DATETIME) between 
                cast(h.startdatetime as DATETIME) 
                and cast(h.enddatetime as DATETIME)
           )

SQL FIDDLE DEMO

Answer 1

For all examples I made the assumption that the start and end times in BookedSchedules would match up exactly with the StaffSchedules start and end times.

With CTE, similar to the question:

I would not recommend using this query, but it may be helpful since it is similar to the query in the question. It is not very readable.

with NonBookingSlots as
(
  select null as StaffId,StartdateTime,EndDateTime from Holidays
  union all
  select StaffId,StartdateTime,EndDateTime from BookedSchedules
)

select
  StaffId, StartdateTime, EndDateTime
from 
  StaffSchedule
where
  not exists(
    select
      1
    from 
      NonBookingSlots
    where
      StaffSchedule.StaffId = isnull(NonBookingSlots.StaffId,StaffSchedule.StaffId)
      and (
        (
          StaffSchedule.StartDateTime =  NonBookingSlots.StartDateTime
          and StaffSchedule.EndDateTime = NonBookingSlots.EndDateTime
        ) or (
          StaffSchedule.StartDateTime <  NonBookingSlots.EndDateTime
          and StaffSchedule.EndDateTime > NonBookingSlots.StartDateTime
        )
     )
  ) 

SQL Fiddle: http://sqlfiddle.com/#!3/9cbf4/14

Without CTE:

This version is more readable in my opinion.

select
  StaffId, StartdateTime, EndDateTime
from 
  StaffSchedule
where
  not exists(
    select
      1
    from 
      BookedSchedules
    where
      StaffSchedule.StaffId = BookedSchedules.StaffId
      and StaffSchedule.StartDateTime =  BookedSchedules.StartDateTime
      and StaffSchedule.EndDateTime = BookedSchedules.EndDateTime
  ) and not exists(
    select
      1
    from 
      Holidays
    where
      StaffSchedule.StartDateTime <  Holidays.EndDateTime
      and StaffSchedule.EndDateTime > Holidays.StartDateTime
  )

SQL Fiddle: http://sqlfiddle.com/#!3/9cbf4/15

With Foreign Key - What I recommend:

If the BookedSchedules always match a StaffSchedule you should use a foreign key to the StaffSchedule instead of replicating the start and end times in BookedSchedules. This results in a cleaner and more efficient query.

select
  StaffId, StartdateTime, EndDateTime
from 
  StaffSchedule
where
  not exists(
    select
      1
    from 
      BookedSchedules
    where
      StaffSchedule.Id = BookedSchedules.StaffScheduleId
  ) and not exists(
    select
      1
    from 
      Holidays
    where
      StaffSchedule.StartDateTime <=  Holidays.EndDateTime
      and StaffSchedule.EndDateTime >= Holidays.StartDateTime
  )

SQL Fiddle: http://sqlfiddle.com/#!3/8a684/3