Find free rooms for today using mySQL

开发者_JAVA百科If in a mysql table RESERVATIONS there are RPOM_NUMBER, DATE_ARRIVAL and DATE_DEPARTED

How do I find all the rooms that are available today?

---

Checking the rooms that are available at particular moment of time:

select RPOM_NUMBER
from RESERVATIONS r
group by RPOM_NUMBER
having not exists (
    select *
    from RESERVATIONS 
    where
       RPOM_NUMBER = r.RPOM_NUMBER and
       now() between DATE_ARRIVAL and DATE_DEPARTED
)

Checking the rooms that are available at a given period of time (from start_time to end_time):

select RPOM_NUMBER
from RESERVATIONS r
group by RPOM_NUMBER
having not exists (
    select *
    from RESERVATIONS 
    where
       RPOM_NUMBER = r.RPOM_NUMBER and
       {start_time} <= DATE_DEPARTED and
       {end_time} >= DATE_ARRIVAL
)

---

Select RPOM_NUMBER where current_date() not between DATE_ARRIVAL and DATE_DEPARTED;

This might be help you. If you can give details table structure with some data then it will be help full to give proper answer.

---

SELECT RPOM_NUMBER
FROM RESERVATIONS
WHERE (DATE(NOW()) NOT BETWEEN DATE_ARRIVAL AND DATE_DEPARTED)

---

You need another table, maybe called rooms, that holds that info. A row in that table might have columns like room_number, is_occupied, is_reserved, reservation_start_time, reserved_for_how_long, reserved_by_whom. Stuff that refers only to the room.

As you have your reservations (it could be a good, useful table, btw) it's difficult for you to tell whether or not a room is empty or occupied, partly because there's no column for whether or not the guest actually showed up and took the room.

You might also consider a table called guests that holds all the info for, well, each guest.

---

SELECT RPOM_NUMBER
FROM RESERVATIONS
WHERE (CURRENT_DATE() < DATE_ARRIVAL) OR (CURRENT_DATE() > DATE_DEPARTED)

EDIT: Answer to comments regarding insert into table

You can put the query above in a subquery and then do INSERT with INNER JOIN on the returned by subquery data set.

! Not tested a query below, just to show an idea:

INSERT INTO RESERVATIONS
SELECT available.RPOM_NUMBER, CURRENT_DATE(), CURRENT_DATE()
FROM RESERVATIONS r
INNER JOIN
 (SELECT RPOM_NUMBER
     FROM RESERVATIONS
     WHERE (CURRENT_DATE() < DATE_ARRIVAL) OR (CURRENT_DATE() > DATE_DEPARTED)
  ) available ON available.RPOM_NUMBER = r.RPOM_NUMBER

---

You really need a ROOMS table to make this database remotely nice to work with - also with a RPOM_NUMBER (Which I am presuming indentifies a single room).

If you have such a table then this query will give RPOM_NUMBERS free now

SELECT RPOM_NUMBER FROM ROOMS WHERE RPOM_NUMBER NOT IN
(SELECT DISTINCT RPOM_NUMBER FROM RESERVATIONS WHERE 
 DATE_ARRIVAL < NOW() AND DATE_DEPARTED > NOW())

And this will INSERT a row into RESERVATIONS

INSERT INTO RESERVATIONS (RPOM_NUMBER,DATE_ARRIVAL,DATE_DEPARTED) VALUES
 (x, y, z)

(where x,y, and z are the RPOM_NUMBER, DATE_ARRIVAL AND DATE_DEPARTED respectively)

This makes no allowance for the specific time. You will have to think about when you want a room to show as free. If it's 1 minute since the departure time, is it free? Or is it being cleaned?

This also assumes that a reservation will have both an arrival and departure date when inserted. How do you deal with guest that are on an open-ended stay?

This SQL isn't tested and not sure if it will work in mySQL - but it will be something very similar.