I need to write a qry for sql 7/ASP to select a 72hr X 5 matrix of 1/2 hr periods - values need to show booked/unbooked for each of 5 cars for all 72 hours. The problem is the database only stores records for the booked periods.
The query (below) works ok for 1 car & one time slot, but calling this 720 times is not good! - Ideally I need some query that can return all the data in one go, and use the recordset to build a table showing availablity.
How do I write this kind of query ?
SELECT [pk_bookedTimeID] FROM tblBookedTimes INNER JOIN tblBookings ON [tblBookedTimes].[fk_bookingID]=[tblBookings].[pk_bookingID] WHERE [tblBookings].[fk_carID]=" & iCarID & " AND " & "[tblBookedTimes].[bookedTime] = '" & dteDateTime & "'"
then you must have been doing something wrong, like calling the database in a loop
call the database like this:
INNER JOIN tblBookings
WHERE tblBookings.fk_carID=" & iCarID & "
ORDER BY bookedTime
notice that you will get back all the bookedTime values for the car, and they will be in bookedTime sequence
now "cross-tab" the bookedTime values using ASP logic, laying them out into an array with zeroes in the "empty" spots...
there's no way that's going to have bad performance
laying out the results into an array should be easy, because from your original question it looks like all your datetime values were previously entered with times on the half hour (which makes sense for a booking table)
so just retrieve the data in one query (you may want to add a WHERE condition for the right date, and maybe get more info on which car, etc.), and ask an ASP programmer for help with the array