By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,930 Members | 633 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,930 IT Pros & Developers. It's quick & easy.

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

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.
Expand|Select|Wrap|Line Numbers
  1. CREATE TABLE #MySeats
  2. (
  3. SeatRank INT,
  4. Seat NVARCHAR(15)
  5. )
  6.  
  7. INSERT INTO #MySeats (SeatRank, Seat) VALUES (1, 'Row A Seat 1')
  8. INSERT INTO #MySeats (SeatRank, Seat) VALUES (2, 'Row A Seat 2')
  9. INSERT INTO #MySeats (SeatRank, Seat) VALUES (5, 'Row B Seat 1')
  10. INSERT INTO #MySeats (SeatRank, Seat) VALUES (6, 'Row B Seat 2')
  11. INSERT INTO #MySeats (SeatRank, Seat) VALUES (7, 'Row B Seat 3')
  12. INSERT INTO #MySeats (SeatRank, Seat) VALUES (8, 'Row B Seat 4')
  13. INSERT INTO #MySeats (SeatRank, Seat) VALUES (10, 'Row C Seat 2')
  14. INSERT INTO #MySeats (SeatRank, Seat) VALUES (11, 'Row C Seat 3')
  15. INSERT INTO #MySeats (SeatRank, Seat) VALUES (12, 'Row C Seat 4')
  16. INSERT INTO #MySeats (SeatRank, Seat) VALUES (13, 'Row D Seat 1')
  17. INSERT INTO #MySeats (SeatRank, Seat) VALUES (14, 'Row D Seat 2')
  18. INSERT INTO #MySeats (SeatRank, Seat) VALUES (15, 'Row D Seat 3')
  19. INSERT INTO #MySeats (SeatRank, Seat) VALUES (16, 'Row D Seat 4')
  20. INSERT INTO #MySeats (SeatRank, Seat) VALUES (17, 'Row E Seat 1')
  21. INSERT INTO #MySeats (SeatRank, Seat) VALUES (18, 'Row E Seat 2')
  22. INSERT INTO #MySeats (SeatRank, Seat) VALUES (19, 'Row E Seat 3')
  23. INSERT INTO #MySeats (SeatRank, Seat) VALUES (20, 'Row E Seat 4')
  24.  
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.
Jan 18 '09 #1
Share this Question
Share on Google+
8 Replies


Delerna
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



Expand|Select|Wrap|Line Numbers
  1.  
  2. select row,count(row) as numseats 
  3. from #MySeats 
  4. group by row 
  5. having count(row)>=@X 
  6.  

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
  1. select seatrank,'Row' + row +' Seat' + seat as Seat
  2. from #MySeats
  3. join
  4. (   select row,count(row) as numseats 
  5.     from #MySeats 
  6.     group by row having count(row)>=@X
  7. )a on a.row=b.row
  8.  
  9. order by seatrank 
  10.  
Hope these ideas help
Jan 18 '09 #2

Delerna
Expert 100+
P: 1,134
Actually on second thoughts you could use the data as it is to get the row seat count

Expand|Select|Wrap|Line Numbers
  1. select row,count(mid(row,5,1)) as numseats  
  2. from #MySeats  
  3. group by mid(row,5,1)
  4. having count(mid(row,5,1))>=@X  
  5.  

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!
Jan 18 '09 #3

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?
Jan 19 '09 #4

ck9663
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
Jan 20 '09 #5

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.
Jan 20 '09 #6

ck9663
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.

Expand|Select|Wrap|Line Numbers
  1. declare @seat table (seatnum smallint)
  2. declare @NumberOfSeatsNeeded smallint
  3.  
  4. insert into @seat (seatnum) values(1); 
  5. insert into @seat (seatnum) values( 2); 
  6. insert into @seat (seatnum) values( 5); 
  7. insert into @seat (seatnum) values( 6); 
  8. insert into @seat (seatnum) values( 7); 
  9. insert into @seat (seatnum) values( 8); 
  10. insert into @seat (seatnum) values( 10); 
  11. insert into @seat (seatnum) values( 11); 
  12. insert into @seat (seatnum) values( 12); 
  13. insert into @seat (seatnum) values( 13); 
  14. insert into @seat (seatnum) values( 14); 
  15. insert into @seat (seatnum) values( 15); 
  16. insert into @seat (seatnum) values( 16); 
  17. insert into @seat (seatnum) values( 17); 
  18. insert into @seat (seatnum) values( 18); 
  19. insert into @seat (seatnum) values( 19); 
  20. insert into @seat (seatnum) values( 20)
  21.  
  22. set @NumberOfSeatsNeeded = 3
  23.  
  24. select startseat, endseat, (endseat - startseat) + 1 as numberofseats
  25. from
  26. (select
  27. startseat =
  28.     (select top 1 a.seatnum 
  29.     from @seat a
  30.     left join @seat b on a.seatnum = b.seatnum + 1
  31.     where b.seatnum + 1 is null and x1.seatnum > a.seatnum order by 1 desc),  x1.seatnum as endseat
  32. from 
  33.     (select x.seatnum 
  34.     from @seat x
  35.     left join @seat y on x.seatnum = y.seatnum + 1
  36.     where y.seatnum + 1 is not null) x1 
  37. union all
  38. select a.seatnum as startseat, b.seatnum as endseat
  39. from @seat a
  40. inner join @seat b on a.seatnum = b.seatnum) vacantseats
  41. where(endseat - startseat) + 1 = @NumberOfSeatsNeeded
  42. order by 1, 3
  43.  
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
Jan 21 '09 #7

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.
Jan 26 '09 #8

ck9663
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
Jan 26 '09 #9

Post your reply

Sign in to post your reply or Sign up for a free account.