starko starko - 27 days ago 6
MySQL Question

How to find first room having 2 days free between consecutive reservations

I have table like that:

room_id date_reservartion
101 October, 01 2016
101 October, 03 2016
102 October, 02 2016
102 October, 05 2016
103 October, 01 2016
103 October, 02 2016
103 October, 04 2016
104 October, 04 2016


I try find first room with free 2 days free in row.

In this situation answer is room 102 because have reservation on 2-10-2016 and 5-10-2016, 3-10 and 4-10 is free.
Could you help me?

Link to SQL Fiddle

Answer

I am sharing two of my approaches.

Approach #1:

SELECT
    a.room_id
FROM
    booking a
INNER JOIN booking b ON a.room_id = b.room_id
AND a.date_reservartion < b.date_reservartion
WHERE
    NOT EXISTS (

      SELECT 
       *
      FROM booking c 
      WHERE c.room_id = a.room_id 
      AND c.date_reservartion BETWEEN 
                (a.date_reservartion + INTERVAL 1 DAY) AND 
                (b.date_reservartion - INTERVAL 1 DAY)
)
AND DATEDIFF(b.date_reservartion,a.date_reservartion) = 3
ORDER BY a.date_reservartion
LIMIT 1

Approach #2:

Here's another way you can achieve this using MySQL user defined variables.

SELECT 
a.room_id
FROM
(
    SELECT 
    *,
    IF(@prevRoom = room_id, @rn := @rn + 1,
         IF(@prevRoom := room_id, @rn := 1, @rn := 1)
    ) AS rn
    FROM booking
    CROSS JOIN (SELECT @prevRoom := 0, @rn := 1) AS var 
    ORDER BY room_id, date_reservartion
)AS a
INNER JOIN 
(
    SELECT 
    *,
    IF(@prevRoom2 = room_id, @rn2 := @rn2 + 1,
         IF(@prevRoom2 := room_id, @rn2 := 1, @rn2 := 1)
    ) AS rn
    FROM booking
    CROSS JOIN (SELECT @prevRoom2 := 0, @rn2 := 1) AS var 
    ORDER BY room_id, date_reservartion
) AS b

ON a.room_id = b.room_id AND a.rn = b.rn+1
WHERE DATEDIFF(a.date_reservartion,b.date_reservartion) = 3

SEE DEMO

Note:

If you need only the first free room of that kind then add the following two lines in the above query:

ORDER BY a.date_reservartion 
LIMIT 1
Comments