Getting the place number for a specifik ID, from a select list

Heres the thing

Im making a booking application where it is possible to be put on standby. When you book more than there is room fore, you will be put on standby. At the same time there should be a field in the databaserow that will be set to true if you start out with being put on standby. The order of the bookings is being set by the logdate, the one who books first gets the higher place.

When you book, my idea was to first insert the data, then check to see if the place of the booking has exceeded the maximum number

Im coding in vb2005 and can easily make it by coding with the sql:

Sql= "Select nr from booking where date=@.date and ClassID=" & theID

And then make a loop with a reader ( I havent inserted any of basic code in this example!)

while reader.read

X+= 1

If reader.item("Nr") = Thenumber then exit while

End while

If x> Max number then do the Update where Standby=true

But i would like to make it simpler with just making a simple call to database and not making a list to read. Ive put in the vb example to just explain what i would like to do..

If anybody have a better idea it is very welcome!!

Dan, you might try something like this...

insert into Booking
select flight_num, reservation_num,
case
when
(
Select Count(*)
From Booking
Where New.flight_num = Booking.flight_num
) >= MaximumSeats then 'Standby'
else 'Reserved'
end as reservation_status
From NewReservations New

The subquery in the case-when-else construct will determine the number of seats already on the Booking table, and insert the record by placing Standy or Reserved in the reservation_status column . The MaximumSeats for the flight needs to be known, also...

Not sure if you store the new apps in a separate table before inserting into your reservation table, but should give you an idea...

|||

You can use the following Logic...

Update/Insert query .. Where @.RequestedNumber <= (Select Count(Room) From booking Where date=@.date and ClassID=@.ClassID);

Select Case When @.@.RowCount <> 0 Then 'Updated' Else 'StandBy' End as Status

sql