How to choose neighboring places? Landing plan

Question

gentlemen

I have a ticket system. Now I need to select neighboring places when the user requests 2 or 3 tickets. Each ticket has a row and column number. The concept of contiguous places is places on the same line as adjacent column numbers. These tickets are in the sql server database. Any ideas on this algorithm for finding available nearby places?

Hello,

Camilo

Answer 1

WITH    places AS
        (
        SELECT   1 AS row, 1 AS col, 0 AS occupied
        UNION ALL
        SELECT   1 AS row, 2 AS col, 1 AS occupied
        UNION ALL
        SELECT   1 AS row, 3 AS col, 0 AS occupied
        UNION ALL
        SELECT   1 AS row, 4 AS col, 0 AS occupied
        UNION ALL
        SELECT   1 AS row, 5 AS col, 0 AS occupied
        UNION ALL
        SELECT   1 AS row, 6 AS col, 1 AS occupied
        )
SELECT  row, col, len
FROM    (
        SELECT  row, col, COUNT(*) OVER (PARTITION BY row, span) AS len
        FROM    (        
                SELECT  row, col,
                        col - ROW_NUMBER() OVER (PARTITION BY row ORDER BY col) AS span
                FROM    places
                WHERE   occupied = 0
                ) q
        ) q2
WHERE   len >= 3

This query returns all available gaps in places 3or more.

The main idea here is that they columnare adjacent, although all the seats ROW_NUMBERare adjacent only within free spaces:

column  occupied  ROW_NUMBER  diff
1       0         1           0
2       1         -           -
3       0         2           1
4       0         3           1
5       0         4           1
6       1         -           -

column ROW_NUMBER, .

Answer 2

, aginst = + 1:

SELECT ...
FROM Seats A
JOIN Seats B ON A.Row = B.Row AND A.Column = B.Column+1
WHERE A.IsReserved = 0
AND B.IsReserved = 0

3-4 = + 1, +2, +3. , CTE, . .

:

create table Seats (Row int not null
  , Col int not null
  , IsReserved bit not null
  , constraint pkSeatsRowColumn primary key (Row, Col));
go
insert into Seats (Row, Col, IsReserved)
select 1,1,0 union all
select 1,2,0 union all
select 1,3,1 union all
select 1,4,0 union all
select 1,5,0 union all
select 1,6,0 union all
select 2,1,0;

with cteAnchor as (
  SELECT Row, Col, 1 as [length]
  FROM Seats
  WHERE IsReserved = 0)
, cteRecursive as (
  SELECT Row, Col, [length]
  FROM cteAnchor
  UNION ALL
  SELECT c.Row, c.Col, c.[length]+1
  FROM Seats s
  JOIN cteRecursive c
    ON s.Row = c.Row and s.Col = c.Col+c.[length]
  WHERE s.IsReserved = 0)
select * from cteRecursive 

, . 3, WHERE, (1,4), , 2 .

Answer 3

CREATE TABLE #Places(LineNumber INT, ColumnNumber INT, IsOccupied CHAR(1));
GO

DECLARE @NumAdjacent INT;

SET @NumAdjacent = 3;

SELECT * FROM #Places AS p
WHERE @NumAdjacent = (SELECT COUNT(*) FROM #Places AS p1
 WHERE p1.LineNumber = p.LineNumber
 AND p1.ColumnNumber BETWEEN p.ColumnNumber AND p.ColumnNumber + @NumAdjacent - 1
 AND p1.IsOccupied = 'Y');