Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2002
    Location
    UK
    Posts
    4

    Post Unanswered: Booking database - query

    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 & "'"

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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/

  3. #3
    Join Date
    Jul 2002
    Location
    UK
    Posts
    4
    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.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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

  5. #5
    Join Date
    Jul 2002
    Location
    UK
    Posts
    4
    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.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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:
    Code:
    SELECT bookedTime 
      FROM tblBookedTimes 
    INNER JOIN tblBookings 
        ON tblBookedTimes.fk_bookingID
         = tblBookings.pk_bookingID
     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

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

    rudy
    http://rudy.ca/

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •