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.
Thanks for your help
Shaun
Table Definitions:
mysql> DESCRIBE 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 | |
| Rep_ID | int(11) | YES | | NULL | |
| PCT_ID | int(11) | YES | | NULL | |
| Practice_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 | |
| Unavailability_ID | int(2) | YES | | NULL | |
| Task_ID | int(11) | YES | | NULL | |
| Work_Type_ID | int(2) | YES | | NULL | |
| Additional_Notes | text | YES | | NULL | |
| Form_Recieved | char(3) | | | | |
| Section_C | char(3) | | | | |
| Date_Difference | varchar(20) | | | n/a | |
+-------------------------+-------------+------+-----+---------------------+----------------+
21 rows in set (0.00 sec)
mysql> DESCRIBE Users;
+----------------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------------------+--------------+------+-----+---------+----------------+
| User_ID | int(11) | | PRI | NULL | auto_increment |
| Client_ID | int(3) | YES | | NULL | |
| User_Username | varchar(40) | | | | |
| User_Firstname | varchar(50) | YES | | NULL | |
| User_Lastname | varchar(50) | YES | | NULL | |
| User_Password | varchar(20) | YES | | NULL | |
| User_Type | varchar(20) | | | Nurse | |
| User_Email | varchar(100) | YES | | NULL | |
| User_Manager_Email | varchar(100) | YES | | NULL | |
| User_Manager_Email_2 | varchar(100) | YES | | NULL | |
| User_Manager_Email_3 | varchar(100) | YES | | NULL | |
| User_Manager_Email_4 | varchar(100) | YES | | NULL | |
| User_Manager_Email_5 | varchar(100) | YES | | NULL | |
| User_Manager_Email_6 | varchar(100) | YES | | NULL | |
| User_Location | varchar(40) | YES | | NULL | |
| Mobile_Number | varchar(20) | YES | | NULL | |
| Rep_Nurse_1 | int(11) | YES | | NULL | |
| Rep_Nurse_2 | int(11) | YES | | NULL | |
| Approver1_ID | int(11) | YES | | NULL | |
| Approver2_ID | int(11) | YES | | NULL | |
| Booking_Credits | int(11) | YES | | NULL | |
+----------------------+--------------+------+-----+---------+----------------+
21 rows in set (0.00 sec)
mysql> DESCRIBE Projects; +--------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------------+--------------+------+-----+---------+----------------+ | Project_ID | int(11) | | PRI | NULL | auto_increment | | Project_Name | varchar(100) | | | | | | Client_ID | int(11) | | | 0 | | | Rep_Viewable | char(3) | | | Yes | | | User_ID | int(11) | YES | | NULL | | +--------------+--------------+------+-----+---------+----------------+ 5 rows in set (0.00 sec)
mysql> DESCRIBE Tasks; +------------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------+-------------+------+-----+---------+----------------+ | Task_ID | int(11) | | PRI | NULL | auto_increment | | Task_Name | varchar(40) | | | | | | Project_ID | int(11) | | | 0 | | +------------+-------------+------+-----+---------+----------------+ 3 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]
