Check for date overlap

Question

0.00/5 (No votes)

See more:

 i have this date range: 2020-03-12 to 2020-03-13

and then i have the sql table with the following data:
RoomNumber  ReservationStartDate  ReservationEndDate
101	    2020-02-17	          2020-02-22
101	    2020-02-14	          2020-03-22
101	    2020-03-11	          2020-03-14
101	    2020-04-11	          2020-04-14



i want to insert this date range 2020-03-12 to 2020-03-13 into the table only if the dates do not overlap with any of the date ranges in the table. Any one who can help with the sql code to achieve that result?

What I have tried:

I have tried the solution below but it does not work

SQL

<pre>select COUNT(*) 
from ResTest
where roomNumber = 101 and
      ReservationTo > '2020-03-12' and
      ReservationFrom < '2020-03-13' 
	  insert into ResTest (RoomNumber, ReservationFrom , ReservationTo)
    select RoomNumber, resStartDate, resEndDate
    from (values (101, '2020-03-12', '2020-03-13')
         ) v(RoomNumber, resStartDate, resEndDate)
    where not exists (select 1
                      from ResTest
                      where ResTest.RoomNumber = v.RoomNumber and
                            ResTest.ReservationFrom > v.resStartDate and
                            ResTest.ReservationTo < v.resEndDate
                     )

Posted 26-Feb-20 2:58am

sazmuelz

Updated 27-Feb-20 12:36pm

Add a Solution

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2020-02-26T03:22:00

You need to think about your query quite a bit: it's more complex than you allow for.
An overlap can be "from the start", "past the end", "inside", or "outside":

Booked:       XXXXX
Start:      AAAAA
End:            BBBBB 
Inside:        CCC
Outside:    DDDDDDDDD

The only condition where you can insert is when the new period is entirely outside:

Booked:       XXXXX
OK:      AAAAA
OK:                BBBBB

So start by writing a query to just show you legal inserts as that's a lot simpler: start and end before start OR start and end after end.

You can then reverse that to select illegal overlaps very easily.

Richard Deeming · Answer 2 · 2020-02-26T03:59:00

Two date ranges overlap if and only if A.Start ≤ B.End and A.End ≥ B.Start.

Applying that to your query:

SQL

DECLARE @RoomNumber int = 101;
DECLARE @NewReservationFrom date = '20200312';
DECLARE @NewReservationTo date = '20200313';

SELECT
    Count(1)
FROM
    ResTest
WHERE
    RoomNumber = @RoomNumber
And
    ReservationFrom <= @NewReservationTo
And
    ReservationTo >= @NewReservationFrom
;

If Not Exists
(
    SELECT 1 
    FROM ResTest 
    WHERE RoomNumber = @RoomNumber 
    And ReservationFrom <= @NewReservationTo 
    And ReservationTo >= @NewReservationFrom
)
BEGIN
    INSERT INTO ResTest (RoomNumber, ReservationFrom, ReservationTo)
    VALUES (@RoomNumber, @ReservationFrom, @ReservationTo);
END;

MarcusCole6833 · Answer 3 · 2020-02-27T12:37:00

I like to use table variables so I have just produced a list of units where the dates conflict

I have put the room data into an incremental id'ed table with dates then checked them all against each other

I then create the conflict table show which id's conflict.

declare @roomdates table ( id int identity(1,1), roomid varchar(6) , fromD date , toD date, conflicted bit)
declare @tableConflict table( idA int ,idb int )

insert into @roomdates (roomid , fromd, toD , conflicted)
values('101', '2020-02-17','2020-02-22', 0)
insert into @roomdates (roomid , fromd, toD , conflicted)
values('101', '2020-02-14','2020-03-22', 0)
insert into @roomdates (roomid , fromd, toD , conflicted)
values('101', '2020-03-11','2020-03-14', 0)
insert into @roomdates (roomid , fromd, toD , conflicted)
values('101', ' 2020-04-11	',' 2020-04-14', 0)

select * from @roomdates

declare @min int = 0, @max int = 0

select @min = min(id) , @max = max(id) from @roomdates
declare @d1 date , @d2 date
while @min <= @max 
begin
	if @min = 500
	begin
		break
	end
	select @d1 = fromd , @d2 = toD from @roomdates where id = @min
	insert into @tableConflict
	select @min, id from @roomdates where @d1 >= fromD  and @d2 <= toD and id ! = @min
	select @min
set @min = @min + 1
end
select * from @tableConflict

Check for date overlap

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0