Hi,
The following query returns 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 Booked_Hours
FROM Bookings B, Projects P
WHERE B.User_ID = 610
AND B.Booking_Type = "Booking"
AND P.Project_ID = 2
AND B.Project_ID = P.Project_ID
AND NOT ( "2005-01-10" < DATE_FORMAT(Booking_Start_Date, "%Y-%m-%d") OR "2005-01-10" > DATE_FORMAT(Booking_End_Date, "%Y-%m-%d") )
Bookings are related to table Projects by Project_ID and to table Users by User_ID. I need to update this query to cater for another type of booking. The Booking_Type = "Task", and the booking is related to Table Tasks by Task_ID. Each Project will have many Tasks and this is how the booking indirectly relates to a project.
Therefore my question is how can i check how many hours a user has been booked for on a particular date relating to a particular project whatever the type of the booking is? If I am checking for a 'Booking' I need to check that B.Project_ID = P.Project_ID and if I am checking for a 'Task' I need to check that AND B.Task_ID = T.Task_ID AND T.Project_ID = P.Project_ID.
Shaun:
I must admit I spent only 5 minutes studying your problem, so it is very likely that I might have overlooked something. However, at this point it appears to me that you should just use two separate queries and a temporary table (or just post-process in the application) to get the results you need.
-- Sasha Pachev Create online surveys at http://www.surveyz.com/
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
