Re: HELP WITH A DATE QUERY USING 4.0.17
I'm not sure if there is a built-in, but what I usually do for similar things is to use general purpose table to force iteration. E.g. create a table called ITERATE with one column, x, and populate with values 0,1,2,3,4,... -- in your case up to 10 -- and index the column. Then it's easy - I only show the date field here: select DATE_ADD(B.Booking_Start_Date,interval it.x day) from Bookings B,iterate it where B.User_ID = '610' and it.x = 1 and it.x = 10 group by DATE_ADD(B.Booking_Start_Date,interval it.x day) Regards, John -Original Message- From: shaun thornburgh 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? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
HELP WITH A DATE QUERY USING 4.0.17
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| | | -00-00 00:00:00 || | Booking_End_Date| datetime| | | -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]
