3

I have a table with records and a period of time for each record, like reservations for instance. So my records look like this:

Table-reservations id room datefrom dateto 1 'one' '2015-09-07' '2015-09-12' 2 'two' '2015-08-11' '2015-09-02' 3 'three' '2015-06-11' '2015-06-14' 4 'two' '2015-07-30' '2015-08-10' 5 'four' '2015-06-01' '2015-06-23' 6 'one' '2015-03-21' '2015-03-25' ... n 'nth' '2015-06-01' '2015-07-03'

Also there is a table with rooms containing an ID, a roomnumber and a roomtype, like this:

Table-rooms idrooms room roomtype 1 'one' 'simple' 2 'two' 'simple' 3 'three' 'double' ... nx 'nth' 'simple'

As you can see some rooms appear multiple times, but with different periods, because they are booked on various periods. What I need to obtain through SQL is a list of rooms that are available in a given period of time.

So something like(pseudocode):

Select room from table where there is no reservation on that room between 2015-08-13 and 2015-08-26

How can I do this?

So I will have a fromdate and a todate and I will have to use them in a query.

Can any of you guys give me some pointers please?

Right now I use the following sql to obtain a list of rooms that are available NOW

select * from rooms where idrooms not in ( select idroom from rezervations where ((date(now())<=dateto and date(now())>=datefrom)or(date(now())<=dateto and date(now())<=datefrom)) ) order by room
Improve this question
3
  • Is there a separate table of all of your rooms? or do we have to assume this "reservations" table is the only one that exists? Commented Sep 24, 2015 at 18:22
  • There is one, you are right. Consider something like: table rooms with (integer:idroom, varchar():room, varchar():type) Commented Sep 24, 2015 at 18:27
  • update your question to include this as it is vital to getting a full answer Commented Sep 24, 2015 at 18:30

5 Answers 5

Reset to default
2

This might be easier to understand.

Assuming you have another table for rooms.

SELECT * FROM rooms WHERE NOT EXISTS (SELECT id FROM reservations WHERE reservations.room = rooms.id AND datefrom >= '2015-08-13' AND dateto <= '2015-08-26')
Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

This looks perfect. Thanks
Wait. it's not that perfect... I have bad cases. I will ellaborate in next comment
a bad dateto in the first inserted record. The month should be 09 instead of 08. Please fix it before testing it
1

You'll want to check that records don't exist where 'date from' is less than or equal to the end date in your range and 'date to' is greater than or equal to the start date in your range.

select t1.room from reservations t1 where not exists ( select * from reservations t2 where t2.room = t1.room and t2.datefrom <= '2015-08-26' and t2.dateto >= '2015-08-13' ) group by room

You can try it out here: http://sqlfiddle.com/#!9/cbd59/5

I'm new to the site, so it won't let me post a comment, but I think the problem on the first answer is that the operators should be reversed.

As mentioned in a previous comment, this is only good if all of the rooms have a reservation record. If not, better to select from your rooms table like this: http://sqlfiddle.com/#!9/0b96e/1

select room from rooms where not exists ( select * from reservations where rooms.room = reservations.room and reservations.datefrom <= '2015-08-26' and reservations.dateto >= '2015-08-13' )
Improve this answer

4 Comments

For your second query here is my Fiddle: sqlfiddle.com/#!9/39c45/1 and it's not working properly. Explanations can be found in the query part of the Fiddle
You need to reverse your range dates, so that datefrom <= 'range end' and dateto >= 'range start'.
#Kendall thanks, yours is the winner so far. I will do some more tests to make sure, but with my test case it seems to work ok.
I hope it helps. I'll monitor the thread in case the query needs modification.
0 
SELECT a.room FROM yourTable a WHERE a.room NOT IN ( SELECT DISTINCT( b.room ) FROM yourTable b WHERE datefrom >= '2015-08-13' OR dateto <= '2015-08-26' );

It should work to the best of my guesses. If it doesn't; could you please provide a sqlfiddle to check the data on?

Improve this answer

4 Comments

and instead of or?
this is good if there aren't rooms that have never been booked before.
@vkp I don't think so. There might be rooms which had been booked for longer than that.
@JosephDuty Yes, I have considered that possibility; but can't do anything with the data OP provided.
0

Using this SO answer

Consider

range1 : r11 r12 [inputfromDate inputToDate]
range2 : r21 r22 [datefromColumn datetoColumn]

IF r11 <= r22 && r21 <= r12, then the ranges are overlapping.

There are 6 possible case, where two ranges can overlap. But the above two condition itself handles all the 6 possibilities.

If the above condition matches then that means, the dates are overlapping. So I have used not in to get the remaining entries.

select * from <table-name> where id not in ( select id from <table-name> where :inputfromDate <= datetoColumn and datefromColumn <= :inputToDate )
Improve this answer

Comments

0

You might try this :

select * from rooms where room not in( select room from reservations where '2015-09-16' >= datefrom and '2015-09-16' <=dateto and '2015-09-21' >= dateto )

Goodluck!

Improve this answer

2 Comments

Same problem here... periods are overlapping, even if just partially but they are overlapping. Please check this fiddle: sqlfiddle.com/#!9/7f7ed/1
@user1137313 I updated the answer. please take a look.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.