Select * from table where desired period does not overlap with existing periods

Question

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

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? — Joseph Duty, Sep 24 '15 at 18:22
There is one, you are right. Consider something like: table rooms with `(integer:idroom, varchar():room, varchar():type)` — user1137313, Sep 24 '15 at 18:27
update your question to include this as it is vital to getting a full answer — Joseph Duty, Sep 24 '15 at 18:30

score 2 · Answer 1 · edited Sep 24 '15 at 18:28

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')

edited Sep 24 '15 at 18:28

Lukasz Szozda

162,964
23
234
275

answered Sep 24 '15 at 18:24

Aᴍɪʀ

7,623
3
38
52

This looks perfect. Thanks – user1137313 Sep 24 '15 at 18:46
Wait. it's not that perfect... I have bad cases. I will ellaborate in next comment – user1137313 Sep 24 '15 at 19:01
a bad dateto in the first inserted record. The month should be 09 instead of 08. Please fix it before testing it – user1137313 Sep 24 '15 at 19:30

Kendall · Accepted Answer · 2015-09-24T19:29:17.097

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'
)

edited Sep 24 '15 at 19:29

answered Sep 24 '15 at 19:13

Kendall

381
1
9

For your second query here is my Fiddle: http://sqlfiddle.com/#!9/39c45/1 and it's not working properly. Explanations can be found in the query part of the Fiddle – user1137313 Sep 24 '15 at 19:43
You need to reverse your range dates, so that datefrom <= 'range end' and dateto >= 'range start'. – Kendall Sep 24 '15 at 19:50
#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. – user1137313 Sep 24 '15 at 19:56
I hope it helps. I'll monitor the thread in case the query needs modification. – Kendall Sep 24 '15 at 20:02

score 0 · Answer 3 · answered Sep 24 '15 at 18:21

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?

answered Sep 24 '15 at 18:21

hjpotter92

78,589
36
144
183

`and` instead of `or`? – Vamsi Prabhala Sep 24 '15 at 18:24
1

this is good if there aren't rooms that have never been booked before. – Joseph Duty Sep 24 '15 at 18:25
@vkp I don't think so. There might be rooms which had been booked for longer than that. – hjpotter92 Sep 24 '15 at 18:26
@JosephDuty Yes, I have considered that possibility; but can't do anything with the data OP provided. – hjpotter92 Sep 24 '15 at 18:26

score 0 · Answer 4 · edited Jun 20 '20 at 09:12

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
)

Hotdin Gurning · Answer 5 · 2015-09-24T20:17:58.283

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!

edited Sep 24 '15 at 20:17

answered Sep 24 '15 at 19:27

Hotdin Gurning

1,821
3
15
24

Same problem here... periods are overlapping, even if just partially but they are overlapping. Please check this fiddle: http://sqlfiddle.com/#!9/7f7ed/1 – user1137313 Sep 24 '15 at 19:53
@user1137313 I updated the answer. please take a look. – Hotdin Gurning Sep 24 '15 at 20:18

Select * from table where desired period does not overlap with existing periods

5 Answers5