Re: A Complicated SELECT query - Sasha Pachev - com.mysql.lists.mysql

2 messages in com.mysql.lists.mysqlRe: A Complicated SELECT query

From	Sent On	Attachments
shaun thornburgh	29 Dec 2004 15:18
Sasha Pachev	30 Dec 2004 15:32

Subject:	Re: A Complicated SELECT query
From:	Sasha Pachev (sas...@surveyz.com)
Date:	12/30/2004 03:32:46 PM
List:	com.mysql.lists.mysql

shaun thornburgh wrote:

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.