mysql - SQL get consecutive starting and end date with specific period

Question

I have a hotel_availablities table something like this.

date	availability
2021-01-15	y
2021-01-16	y
2021-01-17	y
2021-01-18	n
2021-01-19	n
2021-01-20	y
2021-01-21	n
2021-01-22	y
2021-01-23	y

question from:https://stackoverflow.com/questions/66058598/sql-get-consecutive-starting-and-end-date-with-specific-period

Answer 1

This is a gaps and islands problem. Assuming you are using MySQL 8+, we can use the difference in row numbers method here:

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (ORDER BY date) rn1,
              ROW_NUMBER() OVER (PARTITION BY availability ORDER BY date) rn2
    FROM yourTable
)

SELECT MIN(date) AS start_date, MAX(date) AS end_date, COUNT(*) AS cnt
FROM cte
WHERE availability = 'y'
GROUP BY rn1 - rn2
HAVING COUNT(*) >= 2;    -- but change to COUNT(*) >= 3, e.g. for three days in a row

Demo

Note that my query does not give the exact output you expect, but maybe this would be enough for your requirement. If you wanted to break out each island larger than 2 days in terms of pairs of 2 days at a time, you might have to also bring in a calendar table here.