Find the overlapping date or time range in SQL Server

Recently I was working on a project, where the company wanted to track and manage all its assets and infrastructure in multiple locations. The tracking and management were done for all the types of assets including rooms, cubicles, and cabins tracking for the full current financial year. They also had a provision wherein the employee or any other person having access to the portal can request asset booking for a particular date and time range. This was to be approved by the respective asset manager on the request received. However, if the requested asset is already booked by some other uses for the same date-time range or in the conflicting date-time range then, the application should show an error when accepting and validating the user's request. Let's tone down the above case for a simple illustration. We have a room which we want to rent out, The room can be rented for a maximum of a week. So let's create a room_series table to track a room for the week with 5 min granularity. Since our booking times are rounded off to the nearest 5 min.

drop table if exists room_series
create table room_series(
    id int identity(1,1) primary key,
    day int,
    start_time time(0),
    end_time time(0),
    time_id int,
    room_id int,
    is_booked bit not null default(0)
);

with weeks (day) as (
    select 1
    union all
    select day + 1
    from weeks
    where day < 7
), five_min_series (start_time, end_time, time_id) as (
    select cast ('00:00:00' as time(0)) as start_time, cast ('00:05:00' as time(0)) as end_time, 1 as level
    union all 
    select
        cast(DATEADD(mi, 5, start_time) as time(0)) as start_time,
        cast(DATEADD(mi, 5, end_time) as time(0)) as end_time,
        time_id + 1
    from five_min_series t1
    where time_id < 288 -- their are 288 block of 5 munities in a 24 hrs.
), rooms (room_id) as (
    select 1
    union all
    select room_id + 1
    from rooms where room_id < 2
)
insert into room_series (day, start_time, end_time, time_id, room_id) 
select day, start_time, end_time, time_id, room_id from weeks, five_min_series, rooms
 option(maxrecursion 0); -- populating records in room_series table

 select * from room_series -- 4032 records

We have created a room_series table that holds records for rooms number 1 and 2 for the 7 days of the week with 5 min granularity. This room_series table contains a column for day reference for a day of the week, start_time, end_time, time_id, and room_id reference for the room number, and the is_booked column default is 0, meaning the particular room_series is not booked and is available for booking or renting as in our case. Now to simulate the situation of finding or validating the request for room booking for a particular day and for a particular time. let's consider that someone has already reserved room number 1 for day 2 and for a start time '08:00:00' am till the end time '16:30:00' pm, we need to simulate this with the below update statement.

update room_series set is_booked = 1 where room_id = 1 and day = 2 and start_time >= '08:00:00' and end_time <= '16:30:00';

Room number 1 is booked or reserved by someone for day 2 and for the time between '08:00:00' and '16:30:00'. Now suppose, we received the asset booking request for room number 1 for the same day i.e day number 2, and was requested for the time between '16:00:00' pm till '21:00:00' pm. With the new request received the request, we need to find that whether his request is overlapping with any other already approved requests. We know that the new request is already overlapping with the booked request for the time frame of '16:00:00' till '16:30:00' pm, due to this half-hour overlap we have to show the user that his request is overlapping/ conflicting and hence canceled. There are 4 cases where we can say that the overlap can occur and they are partial overlap either at the beginning or at the end, complete overlap, and complete within limits, let's consider these scenarios with our illustration, and also for simplicity lets consider the booked room timing as EventA and request for room booking as EventB.

1. When EventB start_time is less than EventA start_time and EventB end_time is greater than or equal to EventA start_time. Essentially when EventB starts before EventA but also ends before EventA. It's a case of partial overlap for the beginning part.

2. When EventB start_time is greater than or equal to EventA start_time AND EventB end_time is less than or equal to EventA end_time. meaning EventB starts at the same time or after EventA starts AND EventB ends before or at the same time as EventA. A case of complete overlap.

3. Similar to case 2 when EventB starts after EventA AND ends before EventA, where the EventB is completely contained within the boundaries of EventA.

4. When EventB start_time is less than or equal to EventA end_time AND EventB end_time is greater than EventA end_time. It means EventB starts before or at the same time as the end of EventA AND EventB ends after EventA. It's a case of partial overlap for the ending part.

But for checking if there is any overlap occurrence, we need to keep in mind two cases.

1. EventB end_time is greater than or equal to EventA start_time.

2. EventB start_time is less than or equal to EventA end_time. Let's check it in our case with the below query

declare @eventB_start_time time(0) = '16:00:00' , @eventB_end_time time(0) = '21:00:00';
select * from room_series where day = 2 and room_id = 1 and start_time <= @eventB_end_time and end_time >= @eventB_start_time and is_booked = 1;

The above result set shows the overlapping or conflicting rows between EventA and EventB. In our case, we knew that there is an overlapping time range from '16:00:00' pm till '16:30:00' pm as eventA is already booked for that time range and eventB also includes that time range in its request for booking, for the same day and the same room number. Note we can adjust the comparison operator as per our requirement in the query. To further optimize, if we don't want to return overlapping rows and show them to the user and only want to check if the user request is overlapping or not. We can use the 'Where NOT EXISTS' clause and the inner query returning the overlapping rows. Below is the snippet.

declare @eventB_start_time time(0) = '16:00:00' , @eventB_end_time time(0) = '21:00:00';
select 1 as is_overlapped
where exists 
(
    select * from room_series where day = 2 and room_id = 1 and start_time <= @eventB_end_time and end_time >= @eventB_start_time and is_booked = 1
);

With this, we return the flag in case we get the overlapping rows between the date or time range as per our requirement.