Hi,

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 ?

Thanks
---------

dteDateTime="27-Jul-02 15:30"
iCarID=3

SELECT [pk_bookedTimeID] FROM tblBookedTimes INNER JOIN tblBookings ON [tblBookedTimes].[fk_bookingID]=[tblBookings].[pk_bookingID] WHERE [tblBookings].[fk_carID]=" & iCarID & " AND " & "[tblBookedTimes].[bookedTime] = '" & dteDateTime & "'"

can you give a brief example of what the result set is supposed to look like with the holes in it?

have a look at Finding all the dates between two dates which shows how to use an integer table to generate dates

rudy
http://rudy.ca/

Hi,

The results would be as follows :

car_id | 24/07/02 00:00 | 24/07/02 00:30 | 24/07/02 01:00 ...
1 0 1 1
2 1 1 1
3 1 1 1

Each row would be a car, each column a date/time.
The date/times would cover 72 hrs ( 144 columns)
from any date/time specified.

The problem I have is that I only store the 1's in the db not the 0's!

The article looks like exactly what Im trying to do - Thanks.

okay, what you want is a cross-tab report, and since you'll have to do that in asp anyway, there's not much point generating the missing values with sql, just do them with asp too...

rudy

Thanks rudy,

At least I know what its called now! that was half the problem trying to research it.

I have solved this already in ASP, the performance was so bad- thats why I looked into doing it on the db server with SQL.

the performance was bad in ASP?

then you must have been doing something wrong, like calling the database in a loop

call the database like this: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

i guess i should mention that i don't do ASP

rudy
http://rudy.ca/