P: 4

Hi all,
I hope someone can help me out. I need to return the best available seats subject to the constraint that the seats are side by side (or return X consecutive records from a table column where the values are integers). I can do this programmatically (using code and stored procedures), but it's not a neat solution and there are also performance issues.
Returning the best available X number of seats is very straightforward. But I need to do the following,
If there are less than X seats available, return all the seats.
If there are more than X seats available, but they are not consecutive, then return the best X seats.
If there are more than X seats avalable, then return the best X consecutive seats.
Any help would be greatly appreciated. I have a column to my database table called 'SeatRank' which denotes the best seats  the lower the SeatRank value, the better the seat, so a seat with a SeatRank of 1 is the best seat. 
CREATE TABLE #MySeats

(

SeatRank INT,

Seat NVARCHAR(15)

)


INSERT INTO #MySeats (SeatRank, Seat) VALUES (1, 'Row A Seat 1')

INSERT INTO #MySeats (SeatRank, Seat) VALUES (2, 'Row A Seat 2')

INSERT INTO #MySeats (SeatRank, Seat) VALUES (5, 'Row B Seat 1')

INSERT INTO #MySeats (SeatRank, Seat) VALUES (6, 'Row B Seat 2')

INSERT INTO #MySeats (SeatRank, Seat) VALUES (7, 'Row B Seat 3')

INSERT INTO #MySeats (SeatRank, Seat) VALUES (8, 'Row B Seat 4')

INSERT INTO #MySeats (SeatRank, Seat) VALUES (10, 'Row C Seat 2')

INSERT INTO #MySeats (SeatRank, Seat) VALUES (11, 'Row C Seat 3')

INSERT INTO #MySeats (SeatRank, Seat) VALUES (12, 'Row C Seat 4')

INSERT INTO #MySeats (SeatRank, Seat) VALUES (13, 'Row D Seat 1')

INSERT INTO #MySeats (SeatRank, Seat) VALUES (14, 'Row D Seat 2')

INSERT INTO #MySeats (SeatRank, Seat) VALUES (15, 'Row D Seat 3')

INSERT INTO #MySeats (SeatRank, Seat) VALUES (16, 'Row D Seat 4')

INSERT INTO #MySeats (SeatRank, Seat) VALUES (17, 'Row E Seat 1')

INSERT INTO #MySeats (SeatRank, Seat) VALUES (18, 'Row E Seat 2')

INSERT INTO #MySeats (SeatRank, Seat) VALUES (19, 'Row E Seat 3')

INSERT INTO #MySeats (SeatRank, Seat) VALUES (20, 'Row E Seat 4')

When X is 2, seats A1 (Row A Seat 1) and A2 would be returned.
When X is 3, seats B1, B2, B3 would be returned.
when X is 5, seats C2, C3, C4, D1, D2 would be returned. Etc.
I think the solution in something along the lines of getting a count of consecutive seatrank values for each seat, so for example A1 would have a count of 2, A2 would have a count of 1, B1 would have a count of 4 etc. and then getting the min seatrank where it's count value is X.
 
Share this Question
Expert 100+
P: 1,134

How do you know whether a seat is available or not?
Does the seat have to be like 'Row A seat 3' or
can it be 3 columns?
seatrank,row,seat
1............A ....1
2........... A ....2
etc
with that data like that you could have a subquery that selects the count of seats in a row 

select row,count(row) as numseats

from #MySeats

group by row

having count(row)>=@X

you can use the reults of that to filter out all rows where there are not enough seats and order that by rank. 
select seatrank,'Row' + row +' Seat' + seat as Seat

from #MySeats

join

( select row,count(row) as numseats

from #MySeats

group by row having count(row)>=@X

)a on a.row=b.row


order by seatrank

Hope these ideas help
  Expert 100+
P: 1,134

Actually on second thoughts you could use the data as it is to get the row seat count 
select row,count(mid(row,5,1)) as numseats

from #MySeats

group by mid(row,5,1)

having count(mid(row,5,1))>=@X

You may need to have several quries in a stored proc to achieve all of your goals?
You could use the results of some test queries to control if statements so that the
correct one of the set returns the records, if you follow my meaning!
 
P: 4

Hi Delerna.
Many thanks for your response. Unfortunately these queries won't always return X consecutive rows. I need to find X consecutive rows (if they exist). In the data I gave, seats A3 and A4 have been booked and so are no longer available. If the seats are not in the table then they have been booked and cannot be offered to another customer. (This can sometimes happen with a booking system when customers attempt to book tickets concurrently and at least one customer cancels their booking before completion.)
Maybe I should have phrased the questions as how can I find the first X consecutive numbers in the column SeatRank?
So if I had a column with the following values,
1, 2, 5, 6, 7, 8, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20
if X = 2, the values 1 and 2 would be returned (as the first two numbers are consecutive)
if X = 3, the values 5, 6, 7 would be returned (as these are the first series of three consecutive numbers)
if X = 4, the values 5, 6, 7, 8 would be returned (as these are the first series of four consecutive numbers)
if X = 5, the values 10, 11, 12, 13, 14 would be returned (as these are the first series of five consecutive numbers)
etc.
Any ideas?
  Expert 2.5K+
P: 2,878

Based on your example, you have a column that contains only the available seats. Do you have access to the table with all the seat numbers and the occupied seats are just marked?
CK
 
P: 4
 @ck9663
Hi CK. No, we only have a column with the available seats. When a seat is booked the record is removed from this table and inserted into a new (shopping cart) table.
  Expert 2.5K+
P: 2,878

You made me sweat on this one...
Try this, change the value of @NumberOfSeatsNeeded variable and add more rows to test it. 
declare @seat table (seatnum smallint)

declare @NumberOfSeatsNeeded smallint


insert into @seat (seatnum) values(1);

insert into @seat (seatnum) values( 2);

insert into @seat (seatnum) values( 5);

insert into @seat (seatnum) values( 6);

insert into @seat (seatnum) values( 7);

insert into @seat (seatnum) values( 8);

insert into @seat (seatnum) values( 10);

insert into @seat (seatnum) values( 11);

insert into @seat (seatnum) values( 12);

insert into @seat (seatnum) values( 13);

insert into @seat (seatnum) values( 14);

insert into @seat (seatnum) values( 15);

insert into @seat (seatnum) values( 16);

insert into @seat (seatnum) values( 17);

insert into @seat (seatnum) values( 18);

insert into @seat (seatnum) values( 19);

insert into @seat (seatnum) values( 20)


set @NumberOfSeatsNeeded = 3


select startseat, endseat, (endseat  startseat) + 1 as numberofseats

from

(select

startseat =

(select top 1 a.seatnum

from @seat a

left join @seat b on a.seatnum = b.seatnum + 1

where b.seatnum + 1 is null and x1.seatnum > a.seatnum order by 1 desc), x1.seatnum as endseat

from

(select x.seatnum

from @seat x

left join @seat y on x.seatnum = y.seatnum + 1

where y.seatnum + 1 is not null) x1

union all

select a.seatnum as startseat, b.seatnum as endseat

from @seat a

inner join @seat b on a.seatnum = b.seatnum) vacantseats

where(endseat  startseat) + 1 = @NumberOfSeatsNeeded

order by 1, 3

It will give you the start seat number and end seat number. You can use another module to loop through the numbers or handle it on your front end to move it to your cart.
Also, it will not return the first available seats. It will return all available seats. Just use the TOP clause if you need it to return a single row.
Happy coding!
 CK
 
P: 4

Hi CK,
I've been playing around with your code  it's great and works perfectly. Unfortunately when I adapt the query for the database I'm working on (which has a couple of other table dependencies and hundreds of thousands of rows) on performance is very slow (well over ten minutes). I'm playing around with variations on the code but so far I've not had any success. I think it's back to the drawing board  maybe to try and establish the islands of consecutive numbers. I'll post any breakthroughs here. If you have any further thoughts I'd be delighted to hear them.
  Expert 2.5K+
P: 2,878

Hundreds of thousands of rows? What kind of airline, stadium or anything with seat on it has hundreds of thousand of rows? :)
Anyway, given the limited information you gave, I'd say try creating the proper index to your underlying tables.
Happy coding!
 CK
    Question stats  viewed: 11112
 replies: 8
 date asked: Jan 18 '09
