CODEWITHSUNDEEP
mysql
Ben Weston
Ben Weston

Reputation: 21

Select date range between bookings

I have a bookings for properties table, the schema being:

property_id, start_date, end_date

This has a list of the property IDs (referenced in another table) and the start_date and end_date of every booking for each property.

What I'm trying to work out is a query that would show all properties (DISTINCT) available for a 7 day stay within the next 28 days.

I can't work out how I'd begin to structure this query.

Upvotes: 2

Views: 161

Answers (1)

Vinko Vrsalovic
Vinko Vrsalovic

Reputation: 340241

You need to do a cross join (join the table against itself) and compare the finish date of one record against the start date of another record of the same property.

Quick sample to get you started (missing the within 28 days condition and not guaranteed to work in every case):

create table bookings(id int, start datetime, finish datetime);

insert into bookings values (1, '2015-05-18','2015-05-23');
insert into bookings values (1, '2015-05-25','2015-06-01');
insert into bookings values (2, '2015-05-18','2015-05-29');
insert into bookings values (2, '2015-06-15','2015-06-21');

select distinct a.id  
from bookings a cross join bookings b 
where 
a.id = b.id and a.start != b.start and datediff(a.start,b.finish) >= 7;

Upvotes: 1

Related Questions