Finding sequences in a query? 1

[bartus991]

I'm am making a small programm for seat reservations. But I have a problem to find the sequence in the available seats. I will explain it with the following sample:

Row Seat Total Seats In Sequence Sequence number Available
1 1 3 1 3
1 2 3 2 2
1 3 3 3 1
1 6 4 1 4
1 7 4 2 3
1 8 4 3 2
1 9 4 4 1
2 1 3 1 3
2 2 3 2 2
2 3 3 3 1
2 8 2 1 2
2 9 2 2 1

If have tried to do it with Dcount but this function is very slow. The output of available seats is the row that I use for the rest of the database.

Please let me know if you have a better solution to get the availeble seats next to each other as an output.

For example if I need 3 seats the system have to give me to following options:

Row Seat Total Seats In Sequence Sequence number Available
1 1 3 1 3
1 6 4 1 4
1 7 4 2 3
2 1 3 1 3

Hope that you can help me, maybe Ecount works better but I can't figure out I can make it work for my query.

Best regards
Bart

 

[tedsmith]

How about using instr on a string? This would be far faster than any SQL and you only need two fields in this small table, one with the Row_ID number and the other with this SeatString. Use a linked relational second table with a row for every seat to store details of the booked seat such as Row_ID, Name, Address & price. Only use the link after you have booked the seat.

Have one row in the table for each row of seats with a string with a character for every seat eg."00000000000000000000000000000" means every seat in a row of 40 is available.

When you book a seat make a routine to change the corresponding number to a 1 and store it in the database
eg. "001100110011000000000000000000000000" means seats 3,4,7,8,11,12 are taken.

You can easily find consequetive seats of any number.
To find the seat number first available seats simply say either:-
Pattern="0" 'for single
Pattern="00" 'for doubles
Pattern="000" 'for triples
Then:-
Start=0
SeatNumber=Instr(Start, SeatString,Pattern). Use "00" for pairs or any number of 000's for the seats required.
You could even find seats closer to the center by doing this is a loop and making Start=SeatNumber you just found each time, until it was closest to the middle seat number. Middle is Len(SeatString)/2, then exit the loop.

Then to book say one seat SeatNumber:
SeatString=Left(SeatString,Seatnumber-1) & "1" & mid(SeatString,Seatnumber+1).
Then Store the string in the table & enter the personal details in the second linked table.

To count total seats just count all the 1's in a loop using:
If Mid(Seatstring,Seatposition,1)-"1" then
Total=Total+1
Show seat occupied Picture(indexed) in map
else
Show seat vacant Picture(indexed) in map
end if
when you are displaying the seat map on the screen.

These would be almost instant in operation and makes it very easy and quick to automatically suggest the best seat available by starting on the center of the best row and work outwards, taking into account the end seats of the first rows are not always very good.

 

[MajP]

I think that is an interesting idea but I strongly doubt would that be faster than a Sql solution to find all available sequences, if that is what you are suggesting. If you are looking to return all available possible sequences of N or N+ as the original OP asked, you would have to loop the recordset of rows, then loop the string looking for N and N+ sets, and use string functions on the loop, and then convert this output into some format. No way is that faster.

 

[tedsmith]

I find sorting small arrays vastly faster than doing it in SQL.
SQL usually gets quicker with very large tables but this is a very small one and only because of the indexing allows access directly to the area where the data is without having to interrogate from the start.
I would initially load all rows from the current database into an array SeatsArray(SeatNumber,RowNumber) and only work on the array, storing only each change in seats as you made it.
RowNumber becomes a sort of index allowing quick retrieval.

Each search would take only milliseconds to search the whole theater for any combination.
The SeatNumber and RowNumber would match the actual seat in the theater.

You could also have different numbers representing tentative booking, reserved, paid etc Eg.
000000000
000111000
002133310
000111200
000000000
Shows at a glance the status of the seats.

The use of a separate linked seat and details tables allows easier for other things like mailout lists and regular customers and both tables would be quite small.