This post briefs how to get consecutive available seats in a row using SQL query for a multiplex cinema theatre that stores its data into a SQL Server database. In other words, we need to write a query to get n number of available consecutive seats for the multiplex seat booking application. However, for this demo, we have oversimplified the database design and we have considered only two tables as part of the booking system. These tables are – SeatMaster and SeatBooking. So, let’s have a detailed look at the tables and their schemas.
Table 1 – dbo.SeatMaster
This table stores the seat details and arrangement and the schema of the table is as:
SeatId – Stores unique id of the seat on the table. This is an integer value.
SeatRow – SeatRow id for the seats. We have 10 rows in total.
Section – Section of the seat row in which the seat is located. We have 2 sections in each row.
SeatNumber – Seat number in the given section starting from A-1 to A-50 and B-1 to B-50. We have 50 seats in each section.
SeatType – The type of the seat; Gold or Silver. The first two rows are assigned as Gold and the rest as Silver.
We have 10 seat rows and each row has 2 sections – Section A and Section B. So, each section has 5 seats in it. As a result, in each show, we have 100 available seats. In short, the seat arrangement looks something like this:
We can use the below code to create this seat master table with the sample data.
CREATE TABLE dbo.SeatMaster
(
SeatId INT IDENTITY(1,1) PRIMARY KEY,
SeatRow TINYINT,
Section CHAR(1),
SeatNumber VARCHAR(5),
SeatType VARCHAR(10)
)
GO
--We have total 10 rows and each row has 2 sections. In each section we have 5 seats.
--The total number of seats in the cinema theatre is 100
INSERT INTO dbo.SeatMaster
(SeatRow, Section, SeatNumber, SeatType)
SELECT
tblSeatRow.SeatRow, tblSection.Section,
tblSection.Section
+ '-'
+ CAST(ROW_NUMBER() OVER(PARTITION BY tblSection.Section ORDER BY tblSeatRow.SeatRow, tblSection.Section, tblSeatNumber.SeatNumber) AS VARCHAR(5))
AS SeatNumber,
CASE WHEN tblSeatRow.SeatRow <=2 THEN 'Gold' ELSE 'Silver' END AS SeatType
FROM
(
SELECT 1 AS SeatNumber UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5
)tblSeatNumber
CROSS JOIN
(
SELECT 'A' AS Section UNION ALL SELECT 'B'
)tblSection
CROSS JOIN
(
SELECT 1 AS SeatRow UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7
UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10
)tblSeatRow
ORDER BY SeatRow, Section
GO
SELECT * FROM dbo.SeatMaster
GO
The table and its sample data look like this:
Table 2 – dbo.SeatBooking:
This table stores the seat booking details for a given date and showtime. Also, the schema of the table is as:
BookingId – Unique id for each booking. This is an integer and auto-incremented field.
BookingDate – The date for which the seat has been booked.
ShowTime – The show time for which the ticket has been booked on the given date.
CustomerName – Name of the customer who books the ticket.
BookedAtDateTime – The date and time value on which the ticket gets booked. The tickets can be booked in advance also.
SeatId – The seat id which is booked for the given date. It references the seat master table with a foreign key.
Amount – The amount paid for the seat booking. We have used 350 for gold and 250 for silver seats.
CurrentStatus – The status of the seat booking. It shows booked when a ticket gets booked and is not canceled.
--This is the booking table
CREATE TABLE dbo.SeatBooking
(
BookingId INT IDENTITY(1,1) PRIMARY KEY,
BookingDate DATE NOT NULL,
ShowTime VARCHAR(25) NOT NULL,
CustomerName VARCHAR(256) NOT NULL,
BookedAtDateTime DATETIME NOT NULL,
SeatId INT REFERENCES dbo.SeatMaster(SeatId),
Amount DECIMAL(18,2),
CurrentStatus VARCHAR(25)
)
GO
INSERT INTO dbo.SeatBooking
(BookingDate, ShowTime, CustomerName, BookedAtDateTime, SeatId, Amount, CurrentStatus)
SELECT
CAST('20210402' AS DATE) AS BookingDate,
'Evening 6-9' AS ShowTime,
'Customer - ' + LEFT(ABS(CHECKSUM(NEWID()) % 40), 2) AS CustomerName,
'20210402' AS BookedAtDateTime,
SeatId AS SeatId,
CASE WHEN SeatType = 'Gold' THEN 350 ELSE 250 END AS Amount,
'Booked' AS CurrentStatus
FROM dbo.SeatMaster
WHERE SeatMaster.SeatNumber NOT IN
('A-6', 'A-7', 'A-8', 'A-9', 'A-10',
'B-26', 'B-27', 'B-28', 'B-29', 'B-30',
'A-38', 'A-39', 'A-40', 'B-36', 'B-37',
'B-44', 'B-45', 'A-46', 'A-47', 'A-48')
GO
SELECT * FROM dbo.SeatBooking ORDER BY SeatId
GO
The sample table and its data look like this:
Using the above booking query, we have booked 90 seats out of the 100 seats for the given date. So, we are only left with 10 seats that are available in section A of rows 2 and 6.
Once the booking is done with the above query, the available and booked seats look like this:
Get n consecutive available seats in the same row and section
Suppose, we want to get n number of available consecutive seats in the same row and section. For example, we need to extract 5 consecutively available seats in the same row and section. To do so, we can use the below query:
--Query to get n number of consecutively available seats in the same row and in same section
DECLARE @RequiredSeats TINYINT = 5
;WITH CTEAvailableSeats AS
(
SELECT '2021-04-02' AS BookingDate,
SeatMaster.SeatId, SeatMaster.SeatRow, SeatMaster.Section, SeatMaster.SeatNumber, SeatMaster.SeatType,
CASE WHEN SeatBooking.SeatId IS NULL THEN 'Available' ELSE 'Booked' END AS CurrentStatus
FROM dbo.SeatMaster
LEFT JOIN dbo.SeatBooking
ON SeatMaster.SeatId = SeatBooking.SeatId AND SeatBooking.CurrentStatus = 'Booked' AND BookingDate = '2021-04-02'
WHERE SeatBooking.SeatId IS NULL
)
SELECT DISTINCT SeatMaster.* FROM dbo.SeatMaster
INNER JOIN
(
SELECT
A.BookingDate, A.SeatId, COUNT(1) AS Cnt
FROM
CTEAvailableSeats A
LEFT JOIN CTEAvailableSeats B
ON A.BookingDate = B.BookingDate
AND A.SeatRow = B.SeatRow
AND A.Section = B.Section
AND B.SeatId >= A.SeatId --All rows in the B table coming after the current row
AND B.SeatId <= A.SeatId + (@RequiredSeats -1) --All rows Upto 4 rows in the B table only
GROUP BY A.BookingDate, A.SeatId
HAVING COUNT(1) >= @RequiredSeats
) BookingData
ON SeatMaster.SeatId >= BookingData.SeatId
AND SeatMaster.SeatId <= BookingData.SeatId + (@RequiredSeats - 1)
WHERE BookingDate = '2021-04-02'
GO
Output:
Get n consecutive available seats in the same row irrespective of the sections
Now, suppose we want to get the n available consecutive seats in the same row which may or may not be spanned through multiple sections. To do so, we can use the below query:
--Query to get n number of consecutively available seats in the same row irrespective of the sections
DECLARE @RequiredSeats TINYINT = 5
;WITH CTEAvailableSeats AS
(
SELECT '2021-04-02' AS BookingDate,
SeatMaster.SeatId, SeatMaster.SeatRow, SeatMaster.Section, SeatMaster.SeatNumber, SeatMaster.SeatType,
CASE WHEN SeatBooking.SeatId IS NULL THEN 'Available' ELSE 'Booked' END AS CurrentStatus
FROM dbo.SeatMaster
LEFT JOIN dbo.SeatBooking
ON SeatMaster.SeatId = SeatBooking.SeatId AND SeatBooking.CurrentStatus = 'Booked' AND BookingDate = '2021-04-02'
WHERE SeatBooking.SeatId IS NULL
)
SELECT DISTINCT SeatMaster.* FROM dbo.SeatMaster
INNER JOIN
(
SELECT
A.BookingDate, A.SeatId, COUNT(1) AS Cnt
FROM
CTEAvailableSeats A
LEFT JOIN CTEAvailableSeats B
ON A.BookingDate = B.BookingDate
AND A.SeatRow = B.SeatRow
AND B.SeatId >= A.SeatId --All rows in the B table coming after the current row
AND B.SeatId <= A.SeatId + (@RequiredSeats -1) --All rows Upto 4 rows in the B table only
GROUP BY A.BookingDate, A.SeatId
HAVING COUNT(1) >= @RequiredSeats
) BookingData
ON SeatMaster.SeatId >= BookingData.SeatId
AND SeatMaster.SeatId <= BookingData.SeatId + (@RequiredSeats - 1)
WHERE BookingDate = '2021-04-02'
GO
Output:
Get n consecutive available seats irrespective of rows and sections
Finally, suppose, we want to get the n consecutive seats which may span through multiple rows and sections. To do so, we can use the below query:
--Query to get n number of consecutively available seats irrespective of rows and sections
DECLARE @RequiredSeats TINYINT = 5
;WITH CTEAvailableSeats AS
(
SELECT '2021-04-02' AS BookingDate,
SeatMaster.SeatId, SeatMaster.SeatRow, SeatMaster.Section, SeatMaster.SeatNumber, SeatMaster.SeatType,
CASE WHEN SeatBooking.SeatId IS NULL THEN 'Available' ELSE 'Booked' END AS CurrentStatus
FROM dbo.SeatMaster
LEFT JOIN dbo.SeatBooking
ON SeatMaster.SeatId = SeatBooking.SeatId AND SeatBooking.CurrentStatus = 'Booked' AND BookingDate = '2021-04-02'
WHERE SeatBooking.SeatId IS NULL
)
SELECT DISTINCT SeatMaster.* FROM dbo.SeatMaster
INNER JOIN
(
SELECT
A.BookingDate, A.SeatId, COUNT(1) AS Cnt
FROM
CTEAvailableSeats A
LEFT JOIN CTEAvailableSeats B
ON A.BookingDate = B.BookingDate
AND B.SeatId >= A.SeatId --All rows in the B table coming after the current row
AND B.SeatId <= A.SeatId + (@RequiredSeats -1) --All rows Upto 4 rows in the B table only
GROUP BY A.BookingDate, A.SeatId
HAVING COUNT(1) >= @RequiredSeats
) BookingData
ON SeatMaster.SeatId >= BookingData.SeatId
AND SeatMaster.SeatId <= BookingData.SeatId + (@RequiredSeats - 1)
WHERE BookingDate = '2021-04-02'
Output:
Thanks for the reading. Please share your inputs in the comment section.
