SQL combining multiple schedule availability and limitation information

Question

I am working on a booking application in SQL Server 2016 where the main table has timeslots which can be booked for each staff. There are multiple other tables which provide information on whether a booking can be made (either positive or negative). For example there can be a staff availability table (positive) which shows when staff can be booked , and a holiday table showing when staff can not be booked.

Main table:

CREATE TABLE [tbl_timeslots]
(
    [slotid] [int] ,
    [fromdate] [datetime] ,
    [todate] [datetime] ,
    [staffid] [int] 
)

Positive table (when staff is available)

CREATE TABLE [tbl_availability]
(
    [staffid] [int] ,
    [fromdate] [datetime] ,
    [todate] [datetime] 
) 

Negative table(when staff is not available)

CREATE TABLE [tbl_holidays]
(
    [staffid] [int] ,
    [fromdate] [datetime] ,
    [todate] [datetime] 
) 

Task: find all timeslots from main table where staff is available and not on holiday.

I am looking for a way to combine these multiple positive/negative tables to determine if a specific staff is available for a specific start/end period.

I know this can be done using EXISTS / NOT EXISTS like the example below, but the problem is as soon as you have to iterate over a large number of available options and several positive/negative tables this becomes very slow.

select 
    staffid, fromdate, todate 
from 
    tbl_timeslots 
where 
    exists (select staffid from tbl_availability 
            where tbl_availability.staffid = tbl_timeslots.staffid 
              and tbl_availability.fromdate <= tbl_timeslots.fromdate 
              and tbl_availability.todate >= tbl_timeslots.todate) 
    and not exists (select tbl_holidays.staffid from tbl_holidays 
                    where tbl_holidays.staffid = tbl_timeslots.staffid 
                      and tbl_holidays.fromdate < tbl_timeslots.todate 
                      and tbl_holidays.todate > tbl_timeslots.fromdate)

My question is whether there is a better way tm combine/merge all of these positive/negative tables using SQL to generate one output table showing availability.

Answer 1

You can use the left join as follows:

select distinct staffid, fromdate,todate 
  from tbl_timeslots t
Left join tbl_availability a
       On A.staffid=t.staffid and a.fromdate<=t.fromdate and a.todate>=t.todate
Left join tbl_holidays h
       On h.staffid=t.staffid and h.fromdate<t.todate and h.todate >t.fromdate
Where h.staffid is null and a.staffid is not null