Hi,

I have a table called Bookings which holds start times and end times for appointments, these are held in Booking_Start_Date and Booking_End_Date. I have a page on my site that runs a query to produce a grid to show availiability per day for the next ten days for each user of the system. Users work 8.5 hours a day and the query shows how many hours available the user has on that day:

SELECT 8.5 - (SUM(((DATE_FORMAT(B.Booking_End_Date, '%k') * 60 ) +
DATE_FORMAT(B.Booking_End_Date, '%i')) -
((DATE_FORMAT(B.Booking_Start_Date, '%k') * 60 ) +
DATE_FORMAT(B.Booking_Start_Date, '%i'))) / 60) AS Available_Hours
FROM Bookings B WHERE B.User_ID = '610'
AND NOT ( '2004-07-08' < DATE_FORMAT(Booking_Start_Date, "%Y-%m-%d")
OR '2004-07-08' > DATE_FORMAT(Booking_End_Date, "%Y-%m-%d") )

The problem here is I have to do this query to produce a result for each cell and then for each user so 10 users = 100 queries to load the page!

Is there a way to produce the result so that I only need one query per user so it groups the result by day for the next ten days?

Thanks for your help

_________________________________________________________________
It's fast, it's easy and it's free. Get MSN Messenger today! http://www.msn.co.uk/messenger



-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]



Reply via email to