In a nutshell, I wish to create a query that will help me find the best available seats for a concert type venue much like Ticketmaster.com's "Find Best Available Seats" where the requirements are to find the requested number of seats closest to the stage and the seats must be in sequential order.
Given this example table:
DECLARE @Seats TABLE
(
SectionId INT NOT NULL,
RowId VARCHAR(2) NOT NULL,
SeatId INT NOT NULL,
Priority INT NOT NULL, /* Used to determine closeness to the stage and/or order to search in */
StatusCd CHAR(1) NOT NULL, /* A for Available, H for Held, P for Purchased, etc. */
Cost MONEY NOT NULL
)
And given this script to populate the table:
DECLARE @SectionCounter INT
DECLARE @RowCounter INT
DECLARE @SeatCounter INT
SET @SectionCounter = 1
SET @RowCounter = 1
WHILE @SectionCounter <= 10
BEGIN
WHILE @RowCounter <= 26
BEGIN
SET @SeatCounter = 1
WHILE @SeatCounter <= 26
BEGIN
INSERT INTO @Seats
( SectionId ,
RowId ,
SeatId ,
Priority ,
StatusCd ,
Cost
)
VALUES
( @SectionCounter ,
CHAR(64 + @RowCounter) ,
@SeatCounter ,
1 ,
(
/* Randomly setting certain seats as purchased */
SELECT CASE
WHEN @SeatCounter IN
(
1,2,9,10,
(ROUND(((26 - 1 -1) * RAND() + 1), 0)),
(ROUND(((26 - 1 -1) * RAND() + 1), 0)),
(ROUND(((26 - 1 -1) * RAND() + 1), 0)),
(ROUND(((26 - 1 -1) * RAND() + 1), 0)),
(ROUND(((26 - 1 -1) * RAND() + 1), 0)),
(ROUND(((26 - 1 -1) * RAND() + 1), 0)),
(ROUND(((26 - 1 -1) * RAND() + 1), 0))
)
THEN 'P' ELSE 'A' END) ,
(
SELECT CASE
WHEN @SectionCounter IN (1,2)
THEN 75.00 ELSE 25.00 END
)
)
SET @SeatCounter = @SeatCounter + 1
END
SET @RowCounter = @RowCounter + 1
END
SET @RowCounter = 1
SET @SectionCounter = @SectionCounter + 1
END
What is the best query to find an x number of sequential / contiguous seats?
Below is my current solution, which requires at minimum 3 queries by my application.
For example, if a customer requested 8 of the next best available seats, I would run this query:
/* Get each sections available seat count */
SELECT SectionId,
Priority,
COUNT(SeatId) AS 'Seat Count'
FROM @Seats
WHERE StatusCd = 'A' /* A = Available. */
GROUP BY SectionId, Priority
ORDER BY Priority
Which would produce a result set such as this:
| SectionId | Priority | SeatCount |
|-----------|----------|-----------|
| 1 | 1 | 544 |
| 2 | 2 | 554 |
| 3 | 3 | 552 |
The application would say "Are there 8 seats available with a Priority of 1?" and with the above result set, the answer would be yes, so let's get the available row counts for the corresponding section, which is Section 1. Here's the query for that:
SELECT RowId,
COUNT(SeatId) AS 'Seat Count'
FROM @Seats
WHERE SectionId = 1
AND StatusCd = 'A'
GROUP BY RowId
Which would produce a result set such as this:
| RowId | SeatCount |
|-------|-----------|
| A | 21 |
| B | 18 |
| C | 22 |
The application would look at these results and ask the same question starting with the first row, "Are there 8 seats available in Row A?" With the above results, the answer would be yes, so at that time the application would query for all seats in Row A with this query:
SELECT *
FROM @Seats
WHERE SectionId = 1
AND RowId = 'A'
Which would produce a result set such as this:
| SectionId | RowId | SeatId | Priority | StatusCd | Cost |
|-----------|-------|--------|----------|----------|-------|
| 1 | A | 1 | 1 | P | 75.00 |
| 1 | A | 2 | 1 | P | 75.00 |
| 1 | A | 3 | 1 | A | 75.00 |
| 1 | A | 4 | 1 | A | 75.00 |
| 1 | A | 5 | 1 | A | 75.00 |
At that time, the application would iterate through the results attempting to find 8 seats in a row with a StatusCd of 'A' for available.
I'm sure there is a much more efficient method of querying for sequential records in the database without having to load entire rows and doing it in code.
My best guess for an optimal solution would be to do a self join on the table and doing some kind of incrementing of the SeatId or something along those lines.
Any help or suggestions is greatly appreciated.
This should get you started. You were on the right track as far as the self join, that is another way to this.
This will give you the first 8 seats available with the same priority, section, row with a status of 'A' in order of precedance by priority, section, row. Correct me if I misunderstood anything.
DECLARE @number_seats AS INTEGER = 8;
WITH T1 AS (
SELECT S.*,
SeatId - ROW_NUMBER() OVER(PARTITION BY Priority, SectionId, RowId, StatusCd ORDER BY SeatId) AS grp
FROM #seats AS S
),
T2 AS (
SELECT Priority AS Priority,
SectionId AS Section,
RowId AS RowId,
StatusCd AS StatusCd,
MIN(SeatId) AS StartingSeat,
MAX(SeatId) AS EndingSeat,
COUNT(*) AS Seats
FROM T1
GROUP BY Priority, SectionId, RowId, StatusCd, grp
),
T3 AS (
SELECT TOP 1 *
FROM T2
WHERE T2.Seats >= @number_seats and StatusCd = 'A'
ORDER BY Priority, Section, RowId, StartingSeat
)
SELECT S.*
FROM T3 JOIN #seats AS S ON
(
T3.Priority = S.Priority AND
T3.Section = S.SectionId AND
T3.RowId = S.RowId AND
S.SeatId BETWEEN T3.StartingSeat AND T3.StartingSeat + @number_seats - 1
)
ORDER BY Priority, Section, RowId, StartingSeat
Results:
SectionId RowId SeatId Priority StatusCd Cost
----------- ----- ----------- ----------- -------- ---------------------
1 A 11 1 A 75.00
1 A 12 1 A 75.00
1 A 13 1 A 75.00
1 A 14 1 A 75.00
1 A 15 1 A 75.00
1 A 16 1 A 75.00
1 A 17 1 A 75.00
1 A 18 1 A 75.00