Hi,
The following query works out the amount of hours a user has been booked for on a particular day:
SELECT (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 Hours_Booked FROM Bookings B WHERE B.User_ID = "1" AND NOT ( "2004-11-01" < DATE_FORMAT(B.Booking_Start_Date, "%Y-%m-%d") OR "2004-11-01" > DATE_FORMAT(B.Booking_End_Date, "%Y-%m-%d") ) ;
However it fails if the booking spans more than one day. For example I have a booking that starts at 9am on 2004-10-31 and ends at 10am on 2004-11-02, and for this the query returns one hour, when it should be 24!
Any pointers regarding this would be most apprecited
Shaun
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
