SQL Each row consecutive date available

Question

How to create a query with SQL to get each row consecutive day room available. For example, i would like to find 3 days consecutive day with at least 3 available rooms

Original table

Date       Available rooms 
----       ---------------
01/12/16         4
02/12/16         5
03/12/16         5
04/12/16         5
05/12/16         4
06/12/16         0
07/12/16         3
08/12/16         4
09/12/16         4

with result below

Date      EndDate       
--------  --------
01/12/16  03/12/16
02/12/16  04/12/16
03/12/16  05/12/16
07/12/16  09/12/16

Answer 1

Try using LEAD() function like below query

SELECT d as StartDate, EndDate
FROM
    (SELECT d, 
        CASE 
            WHEN available_rooms > 2 
                AND LEAD(available_rooms,1) OVER(order by d) > 2 
                AND LEAD(available_rooms,2) OVER(order by d) > 2 
            THEN LEAD(d,2) OVER(order by d) 
        END EndDate
    FROM table1 t) t1
WHERE EndDate is not null;

Below is the output I received

Answer 2

SQL DEMO

SELECT T1.[Date], T3.[Date]
FROM Table1 T1
JOIN Table1 T2
  ON T1.[Date] = DATEADD(day, -1, T2.[Date])
JOIN Table1 T3
  ON T1.[Date] = DATEADD(day, -2, T3.[Date])
WHERE T1.[Available rooms] >= 3
  AND T2.[Available rooms] >= 3
  AND T3.[Available rooms] >= 3

OUTPUT