SQL - search for continuous records of a given size

Question

I am working on a reservation system. The user enters the number of seats that they want to reserve, and the database will return a set of recommended places that have not previously been reserved, which corresponds to the number of seats reserved.

For example, if I had a table:

SeatID | Reserved ----------------- 1 | false 2 | true 3 | false 4 | false 5 | false 6 | true 7 | true 8 | false 9 | false 10 | true 

And the user enters that they want to reserve 2 places, I expect the request to return that places (3, 4), (4, 5) and (8, 9) are not reserved and correspond to the specified number of input places. Seats are organized in sections and rows. Continuous seats should be in the same row.

How would I like to structure this query to work in such a way that it finds all available permanent locations matching this input?

Answer 1

Using SQL Server 2005/2008:

 WITH FreeSeatGroups AS ( SELECT S1.SeatID AS StartID, (SELECT MIN(S2.SeatID) FROM Seats S2 WHERE S2.SeatID > S1.SeatID AND S2.Reserved = 1) - S1.SeatID AS FreeSeatCount FROM Seats AS S1 WHERE S1.Reserved = 0 ) SELECT StartID, FreeSeatCount FROM FreeSeatGroups WHERE FreeSeatCount >= 2 

Note that this returns identifiers 3, 4, and 8, as there are two free spots starting at each of these positions.

It also assumes that place identifiers are always consistent. If not, you can go to ROW_NUMBER() .

Answer 2

This solution will only work in SQL Server 2005+ or another product that supports CTE. In addition, I suggested that "Reserved" is stored in the same way as in your example, as a string, not a bit. Finally, I suggested that SeatId was completely consistent:

  With StartSeats As ( Select SeatId + 1 As SeatId From Seats As S Where Reserved = 'true' And Not Exists( Select 1 From Seats As S2 Where S2.SeatId = S.SeatId + 1 And S2.Reserved = 'true' ) And SeatId < ( Select Max(S1.SeatId) From Seats As S1 ) Union All Select SeatId From Seats Where SeatId = 1 And Reserved = 'false' Union All Select SeatId From Seats Where SeatId = ( Select Max(S1.SeatId) From Seats As S1 ) And Reserved = 'false' ) , SeatRanges As ( Select S1.SeatId As FirstOpenSeat, Min(S2.SeatId) As LastReservedSeat From StartSeats As S1 Left Join Seats As S2 On S2.SeatId > S1.SeatId And S2.Reserved = 'true' Group By S1.SeatId ) Select * From SeatRanges Where LastReservedSeat - FirstOpenSeat = 2 

Answer 3

 WITH FreeSeatGroups AS ( select s1.ss StartID,(select min (s2.ss) from test123 s2 WHERE S2.ss >= S1.ss and S2.rr = 1) -s1.ss FreeSeatCount from test123 s1 ) SELECT StartID, FreeSeatCount FROM FreeSeatGroups WHERE FreeSeatCount >= 1