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 ( '2005-04-08' < DATE_FORMAT(Booking_Start_Date, "%Y-%m-%d") OR '2005-04-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(date) and then for each user so 100 users = 1000 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
Here is the table definition:
mysql> desc Bookings;
+---------------------------------------------+-------------+------+-----+---------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------------------------------------+-------------+------+-----+---------------------+----------------+
| Booking_ID | int(11) | | PRI | NULL | auto_increment |
| Booking_Type | varchar(15) | | | Unavailability | |
| User_ID | int(11) | | | 0 | |
| Project_ID | int(11) | YES | | NULL | |
| Booking_Creator_ID | int(11) | YES | | NULL | |
| Booking_Creation_Date | datetime | YES | | NULL | |
| Booking_Start_Date | datetime | | | 0000-00-00 00:00:00 | |
| Booking_End_Date | datetime | | | 0000-00-00 00:00:00 | |
| Booking_Completion_Date | date | YES | | NULL | |
| Booking_Mileage | int(5) | YES | | NULL | |
| Booking_Status | varchar(15) | | | Other | |
| Additional_Notes | text | YES | | NULL | |
+---------------------------------------------+-------------+------+-----+---------------------+----------------+
23 rows in set (0.00 sec)
mysql>
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
