Identify a Sequence and Position in Sequence

[MajP]

I am trying to help someone who posted Thread222-1530185.
I could come up with a pretty simple solution using code, and then some queries but a pure Sql solution would be better.

The OP is has a room seating database, and is searching for a a contigous sequence of seats. So if he needs 4 seats, he wants to return all seats with 4 or more available seats following it.

So the data looks like

Row seat 
1    2    (beginning/end of a 1 seat sequence)  
1    4    (beginning of a 7 seat seq (4-10))
1    5
1    6
1    7
1    8
1    9
1    10
2    1   (beginning of a 10 seat sequence (1-10))
2    2 
2    3 
2    4 
2    5 
2    6 
2    7 
2    8 
2    9 
2    10

Basically the question is given a row number and a seat number, can I build a query that gives a unique identifier to each contiguous sequence (SequenceID), and gives a number for the place in the sequence (SequenceNumber). I can do this easily in code, but can not figure out how to do this is Sql. Any thoughts? Thanks.

Row seat sequenceID sequenceNumber
1    2         1    1
1    4         2    1
1    5         2    2
1    6         2    3
1    7         2    4
1    8         2    5
1    9         2    6
1    10        2    7
2    1         3    1
2    2         3    2
2    3         3    3
2    4         3    4
2    5         3    5
2    6         3    6
2    7         3    7
2    8         3    8
2    9         3    9
2    10        3    10

The sequence ID could be anything as long as it identifies all members of that sequence. The position in the sequence is also important for further queries.

 

[lameid]

I am not sure why you want the results that way. A more attainable question is how do I see all possible ranges of seats?

Since you are talking fixed seats in your auditorium (or you can write code to establish multiple auditoriums), you can make tables to represent all the possible ranges and then turn around and count to determine if the range is valid. This will show multiple sequences though... For a range of 4 seats, it will show 4,3,2 and 1 sequences.

First_Seat
Row
Seat_number
Possible_additional_Seats_In_Range (X seats in row -seat number)

Seat_Possible (list of all possible seat numbers in ANY row)
Seat_Possible

qry_Possible_Seats_and_Ranges

Select First_Seat.Row, First_Seat.Seat_number, First_Seat.Possible_additional_Seats_In_Range + 1 As Seats_IN_Range,Seat_Possible.Seat_Possible 

From First_Seat, Seat_Possible

Where Seat_Possible.Seat_Possible Between First_Seat.Seat_number 
      And {First_Seat.Seat_number + First_Seat.Possible_additional_Seats_In_Range}

Possible Ranges of Seats:

Select Seats.Row, Seats.Seat_number, Seats.Seats_IN_Range, Count(Available_Seats.Seat)

From qry_Possible_Seats_and_Ranges as Seats 
    Left Join Available_Seats ON Available_Seats.Row = Seats.Row And Available_Seats.Seat = Seats.Seat_number

Group By  Seats.Row, Seats.Seat_number, Seats.Seats_IN_Range

Having Count(Available_Seats.Seat) = Seats.Seats_IN_Range

In hindsight, it looks like the logic may be able to be tweaked to reduce arithmetic.

 

[MajP]

Thanks LameID. I actually figured this out. If you follow the original thread you will see my answer, but it was not real pretty. Your post makes me think of a cleaner approach to try. I do not think what you are suggesting will work for what the user wanted.

Possible_additional_Seats_In_Range is dynamic as people are sat, I beleive you are suggesting a static field.

Example
row Seat ASIR
1 1 5
1 2 4
1 3 3
1 4 2
1 5 1

I have 1 sequence of 5 seats. Then I sit someone in seat 3.
now I have two sequences with the remaining available seats.

row Seat ASIR
1 1 2
1 2 1

1 4 2
1 5 1

So now there are no available sequences of 3 or more only 2 sequences of 2.

 

[lameid]

First_Seat
Row
Seat_number
Possible_additional_Seats_In_Range (X seats in row -seat number)

Yes, for Possible_additional_Seats_In_Range I am suggesting a static value. The table is meant to define the maximum possilbe seats there are in a range if the given seat number is the first to begin the sequence.

Seat_Possible is just used to provide possible seats in a row. So if the most seats any row has is 10, it only needs to contain a list of numbers 1-10.

Both these tables are used to net a result set that contains all possible ranges or qry_Possible_Seats_and_Ranges.

Finally I use that query and the Available seats to determine which ranges are valid.

So if you wanted to see which seat ranges have 4 seats available, you could simplly use Seats.Seats_IN_Range = 4 in the having clause of the last query. You would see multiple options for your starting seat if there was a range of 10. The First through 7th seats would be listed in that scenario. My query does not assign an ID, it simply says for the given number of seats, this is a valid first seat.

I hope that is clearer. I haven't digested your queries yet. My scenario requires either a lot of setup or code to populate the base table. I suspect my solution would execute faster but won't produce the exact required results.

 

[lameid]

In your solution, you don't provide the names of your intermediate queries which makes it that much more difficult to follow. Can you post the names for each step in the origninal thread?

 

[MajP]

I will post the actual DB later with the coded solution and the sql solution. Thanks.

 

[MajP]

qryFindSeats shows the solution

http://www.4shared.com/file/88095743/7314c7df/FindSequence.html

 

[JonFer]

I would write a query against the full seating table (as opposed to starting with just the open seats) to show each open seat that is preceded by a taken seat or where the open seat is the first seat in a row - these are your starting seats. In the example provided, you'd get 3 rows:

1 2
1 4
2 1

Then another query to show the open seats that precede a taken seat or where the open seat is the last seat in a row - these are your ending seats.

1 2
1 10
2 10

A third query would select from the first query to get the "starting" open seats with a subquery against the second query to get the minimum "ending" seat that is >= to the starting seat in the same row. You should get rows showing each starting seat and ending seat.

1 2 2
1 4 10
2 1 10

By joining this to a "sequence" table with 10 rows, you can generate the requested output if needed.

 

[MajP]

Thanks that is basically what I did with a little variation. I find the beginning of the sequence like you do, but instead of finding the end of the sequence I find the total number in the sequence. Then determine the sequence number, and remaining seats. Basically the same approach but a different subtraction for determining sequence number and reamaining seats.