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?

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
``````