Show available rooms according to two dates entered (ranges)

Question

Show available rooms according to two dates entered (ranges)

Navigation

#1 by (1 votes)

2

The idea is that entering the date of entry and date of departure; Show the available rooms by comparing with the reservation dates made.

Assuming the following tables and data:

CREATE TABLE Habitaciones (
  habitacion_numero  INT
);

CREATE TABLE Reservas (
  habitacion     INT,
  inicio_fecha   DATE,
  fin_fecha   DATE
);

INSERT INTO Habitaciones (habitacion_numero)
VALUES (1),(2),(3),(4),(5);

INSERT INTO Reservas (habitacion, inicio_fecha, fin_fecha)
VALUES (1, '2017-12-01','2017-12-13'),
       (2, '2017-12-01','2017-12-10'),
       (2, '2017-12-12','2017-12-14'),
       (3, '2017-12-05','2017-12-08'),
       (3, '2017-12-15','2017-12-16'),
       (4, '2017-12-07','2017-12-11'),
       (5, '2017-12-03','2017-12-05');

... if I execute the following SQL:

set @fecha1 = STR_TO_DATE('2017/12/09','%Y/%m/%d');
set @fecha2 = STR_TO_DATE('2017/12/11','%Y/%m/%d');

SELECT  h.habitacion_numero,
        CASE WHEN hr.habitacion_numero is null 
              THEN 'Disponible'
              ELSE 'Reservada'
        END  AS 'Estado'      
        FROM habitaciones h
        left JOIN (SELECT   h1.habitacion_numero
                      FROM habitaciones h1
                       inner join reservas r 
                       on h1.habitacion_numero=r.habitacion
                       WHERE @fecha BETWEEN r.inicio_fecha AND r.fin_fecha or 
                             @fecha2 BETWEEN r.inicio_fecha AND r.fin_fecha
                       AND r.fin_fecha
          ) hr
          on hr.habitacion_numero = h.habitacion_numero;

I get the following wrong result:

habitacion_numero      Estado
-----------------      ------
        1              Reservada
        2              Disponible
        3              Disponible
        4              Reservada
        5              Disponible

The result is wrong because it is obvious that room 2 is not available with the dates of entry, for example.

SQL Fiddle

mysql sql

asked by davabun 20.12.2017 в 00:42

source

1 answer

Progress Bar Dynamic Bootstrap MySQL Join results in SQL Server

score 1 · Accepted Answer

As I left you in the comments, your query has 2 typographical errors:

Override clause AND r.fin_fecha at the end of the subquery
You use @fecha instead of @fecha1

With these 2 corrections, the results seem correct. But in effect, the logic of the query is not correct and will continue to produce erroneous results with other dates of entry. In fact, depending on the data, it is even possible to return duplicate room records due to how your LEFT JOIN works.

The following query corrects the logic that determines if the entry dates overlap with a reservation for a room. Additionally, I use the EXISTS clause to avoid duplicating the room records if more than one record in the reservas table overlaps the entry dates:

set @fecha1 = STR_TO_DATE('2017/12/09','%Y/%m/%d');
set @fecha2 = STR_TO_DATE('2017/12/11','%Y/%m/%d');

SELECT  h.habitacion_numero,
        CASE WHEN hr.habitacion_numero is null 
              THEN 'Disponible'
              ELSE 'Reservada'
        END  AS Estado      
        FROM habitaciones h
        LEFT JOIN habitaciones hr
          ON hr.habitacion_numero = h.habitacion_numero
         AND EXISTS (
            SELECT NULL
              FROM reservas r
             WHERE r.habitacion = hr.habitacion_numero
               AND @fecha1 <= r.fin_fecha
               AND @fecha2 >= r.inicio_fecha
         )
order by h.habitacion_numero;

SQL Fiddle

And by the way, if you only need the list of available rooms, instead of the complete list of rooms with the status Disponible or Reservada , as you have it now, then you can simplify the query:

SELECT h.habitacion_numero
  FROM habitaciones h
 WHERE NOT EXISTS (
         SELECT NULL
           FROM reservas r
          WHERE r.habitacion = h.habitacion_numero
            AND @fecha1 <= r.fin_fecha
            AND @fecha2 >= r.inicio_fecha)
 ORDER BY h.habitacion_numero;

SQL Fiddle