Room availability with date

Question

Hi, I have 2 dining rooms and orders, but my dining room structure is slightly different, in the order table there is a number, date, status, for example:

101,2012-12-10,0 101,2012-12-11,0 101,2012-12-12,1 101,2012-12-13,0 102,2012-12-10,0 102,2012-12-11,0 102,2012-12-12,0 

and I would like to find available rooms between 2012-12-10 and 2012-12-13 according to this request, only number 102 should be returned.

I tried

 SELECT id FROM status WHERE status='0' AND date between '2012-12-10' AND '2012-12-13' GROUP BY id 

it does not work, because even finding only one available line, it returns true for 101

therefore 101 is available for 2012-12-11, and then appears as available, but is not suitable for our data range.

Answer 1

This will select all rooms that do not have a row with status=1 in the interval you selected. Please note that I do not use between : if status=1 on December 13, the room is still available in the interval, so I use >= and < :

 SELECT roomid FROM status WHERE `date` >= '2012-12-10' AND `date` < '2012-12-13' GROUP BY roomid HAVING sum(status.status=1)=0 

If your table may have some missing days, and if this means that the room is not booked, but also unavailable, you can also use this query:

 SELECT roomid FROM status WHERE `date` >= '2012-12-10' AND `date` < '2012-12-13' AND status=0 GROUP BY roomid HAVING count(*)=DATEDIFF('2012-12-13', '2012-12-10') 

which checks that the number of days in the interval is equal to the number of rows with status = 0.

Answer 2

If the date has a time associated with it, you need to do something like this:

 SELECT id FROM status WHERE status='0' AND date between '2012-12-10 00:00:00' AND '2012-12-13 23:59:59' GROUP BY id 

You can also use Date (date) instead of including time, but this may be less efficient.

Answer 3

You should be able to use the following:

 SELECT roomid FROM status s1 WHERE status='0' AND date between '2012-12-10' AND '2012-12-13' and not exists (select roomid from status s2 where status='1' AND date between '2012-12-10' AND '2012-12-13' and s1.roomid = s2.roomid) GROUP BY roomid 

See SQL Fiddle for a demo.

Answer 4

Try the following:

 SELECT rd.* FROM room_details rd INNER JOIN (SELECT DISTINCT roomid FROM bookings WHERE roomid NOT IN (SELECT roomid FROM bookings WHERE STATUS =0 AND DATE BETWEEN '2012-12-10' AND '2012-12-13') ) AS a ON rd.roomid = a.roomid 

Answer 5

The SQL condition BETWEEN will return records in which the expression is within the range of values1 and value2 (inclusive). Therefore, the row row 101,2012-12-10,0 should be returned for the above query.

Answer 6

Try the following:

 SELECT roomid FROM status WHERE 'date_from' <= "2012-12-10" AND 'date_to' > "2012-12-10" AND 'status'= 0;