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]
