How to find X consecutive (integer) values in a table column

gigonomics

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.

Expand|Select|Wrap|Line Numbers

 
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.

Delerna

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

Expand|Select|Wrap|Line Numbers

  
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.

Expand|Select|Wrap|Line Numbers

 
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

Delerna

Actually on second thoughts you could use the data as it is to get the row seat count

Expand|Select|Wrap|Line Numbers

 
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!

gigonomics

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?

ck9663

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

gigonomics

Quote:

Originally Posted by ck9663

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

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.

ck9663

You made me sweat on this one...

Try this, change the value of @NumberOfSeatsNeeded variable and add more rows to test it.

Expand|Select|Wrap|Line Numbers

 
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

gigonomics

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.

ck9663

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

How to find X consecutive (integer) values in a table column

re: How to find X consecutive (integer) values in a table column

re: How to find X consecutive (integer) values in a table column

re: How to find X consecutive (integer) values in a table column

re: How to find X consecutive (integer) values in a table column

re: How to find X consecutive (integer) values in a table column

re: How to find X consecutive (integer) values in a table column

re: How to find X consecutive (integer) values in a table column

re: How to find X consecutive (integer) values in a table column

Similar Microsoft SQL Server bytes

/bytes/development

/bytes/it

/bytes/about