Hi,
The following query produces the number of bookings per project grouped by
week:
SELECT WEEK(Booking_Start_Date) AS "WEEK",
SUM(IF(B.Project_ID = "23", 1,0)) AS `Project A`,
SUM(IF(B.Project_ID = "42", 1,0)) AS `Project B`
FROM Bookings B INNER JOIN Projects P USING (Project_ID)
WHERE B.Booking_Type = "Booking"
AND DATE_FORMAT(B.Booking_Start_Date, "%Y-%m-%d") >= '2005-01-01'
AND DATE_FORMAT(B.Booking_Start_Date, "%Y-%m-%d") <= '2005-12-31'
GROUP BY WEEK;
The problem with this is that if I have no bookings for week 42 for example
then that week is not shown in the result. To get round this I created a
table called Weeks that contains all the week numbers for the year.
However I am not sure how I can join Weeks to Bookings so that all the weeks
show.
Any healp would be greatly appreciated.
TABLE DEF'S:
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 | |
| Rep_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 | |
+---------------------------------------------+-------------+------+-----+---------------------+----------------+
22 rows in set (0.00 sec)
mysql> desc Projects;
+----------------------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra
|
+----------------------------+--------------+------+-----+---------+----------------+
| Project_ID | int(11) | | PRI | NULL |
auto_increment |
| Project_Name | varchar(100) | | | |
|
| Client_ID | int(11) | | | 0 |
|
+----------------------------+--------------+------+-----+---------+----------------+
8 rows in set (0.00 sec)
mysql> desc Weeks;
+-------------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+---------+------+-----+---------+----------------+
| Week_ID | int(11) | | PRI | NULL | auto_increment |
| Week_Number | int(11) | | | 0 | |
+-------------+---------+------+-----+---------+----------------+
2 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]
