CODEWITHSUNDEEP
sqlsql-server
Mark
Mark

Reputation: 7818

Sql querying same table for overlapping dates

I'm trying to write a query where any overlap in a date range will be reported.

I am able to do this with several queries, and loops, but I wondered if it could be made more efficient by just one query, where the table joins to itself.

The table structure is:

CREATE TABLE [dbo].[Rentals](
[ID] [int] IDENTITY(1,1) NOT NULL,
[room_id] [int] NOT NULL,
[check_in] [datetime] NOT NULL,
[check_out] [datetime] NOT NULL,
[customer_ref] [bigint] NULL)

So given the same room_id, I want to be able to query if any other booking with the same room_id falls between the check_in and check_out of another booking with the same room_id (to avoid double bookings).

I have to use normal code, rather than LINQ due to the existing project.

This is what I have so far, but it appears to be returning all records:

SELECT     r1.ID, r1.room_id, r1.check_in, r1.check_out,r1.customer_ref
FROM       tblRental AS r1 INNER JOIN
                  tblRental AS r2 ON 
                     r1.room_id = r2.room_id AND 
                     r1.check_in < r2.check_out AND 
                     r1.check_out > r2.check_in

Can anyone help refine the query to return only the records where the room_id is the same, and there is an overlap?

Thanks for any help,

Mark

Upvotes: 0

Views: 414

Answers (2)

Amit
Amit

Reputation: 46323

I think your problem is that your crossing each row with itself, and that's why your query returns all rows. Make sure r1.ID <> r2.ID:

SELECT     r1.ID, r1.room_id, r1.check_in, r1.check_out,r1.customer_ref
FROM       tblRental AS r1 INNER JOIN
                  tblRental AS r2 ON 
                     r1.room_id = r2.room_id AND 
                     r1.check_in < r2.check_out AND 
                     r1.check_out > r2.check_in AND
                     r1.ID <> r2.ID

Upvotes: 4

Peter
Peter

Reputation: 11

Kill the double rows with r1.id <> r2.id.
You also have a little logical error with the predicates. Use a between and things might be a little clearer. ;)
When you use this query, you might want to output r2.

SELECT r2.ID, r2.room_id, r2.check_in, r2.check_out, r1.customer_ref
FROM tblRentals AS r1 
    INNER JOIN tblRentals AS r2 ON r1.room_id = r2.room_id
    and r2.check_in between r1.check_in and r1.check_out
    and r1.id <> r2.id

Upvotes: 0

Related Questions