Hi,
We have a database that keeps track of days worked and days taken off by
staff. All days worked / taken off are held in a table called Bookings.
Staff work on Projects and each project will have various Work_Types, days
taken off are not related to projects and are held in
Unavailability_Descriptions. I need to produce a capacity report to show
days worked vs time taken off per staff member per month for a particular
project i.e.
January February
John Smith
Work Type 1 12 ...
Work Type 2 5 ...
Work Type 3 5 ...
Sickness 1 ...
Holiday 2 ...
Total Days 19 ...
Capacity 106%
Joe Bloggs
Work Type 1 5 ...
Work Type 2 6 ...
Work Type 3 9 ...
Sickness 1 ...
Holiday 1 ...
Total Days 18 ...
Capacity 100%
...
We say that staff have an average of 18 working days per month availability.
I have managed to show the Days worked in a month with the following query
but am having trouble adding the unavailability and capacity:
SELECT
CONCAT_WS(' ', U.User_Firstname, U.User_Lastname) AS Name,
B.Booking_Type,
WT.Work_Type,
SUM(IF(MONTHNAME(BD.Date) = 'January' AND Year(BD.Date) = '2005', 1, 0)) AS
'January',
SUM(IF(MONTHNAME(BD.Date) = 'February' AND Year(BD.Date) = '2005', 1, 0)) AS
'February',
SUM(IF(MONTHNAME(BD.Date) = 'March' AND Year(BD.Date) = '2005', 1, 0)) AS
'March',
SUM(IF(MONTHNAME(BD.Date) = 'April' AND Year(BD.Date) = '2005', 1, 0)) AS
'April',
SUM(IF(MONTHNAME(BD.Date) = 'May' AND Year(BD.Date) = '2005', 1, 0)) AS
'May',
SUM(IF(MONTHNAME(BD.Date) = 'June' AND Year(BD.Date) = '2005', 1, 0)) AS
'June',
SUM(IF(MONTHNAME(BD.Date) = 'July' AND Year(BD.Date) = '2005', 1, 0)) AS
'July',
SUM(IF(MONTHNAME(BD.Date) = 'August' AND Year(BD.Date) = '2005', 1, 0)) AS
'August',
SUM(IF(MONTHNAME(BD.Date) = 'September' AND Year(BD.Date) = '2005', 1, 0))
AS 'September',
SUM(IF(MONTHNAME(BD.Date) = 'October' AND Year(BD.Date) = '2005', 1, 0)) AS
'October',
SUM(IF(MONTHNAME(BD.Date) = 'November' AND Year(BD.Date) = '2005', 1, 0)) AS
'November',
SUM(IF(MONTHNAME(BD.Date) = 'December' AND Year(BD.Date) = '2005', 1, 0)) AS
'December'
FROM Bookings B, Booking_Dates BD, Users U, Work_Types WT, Projects P
WHERE B.Booking_ID = BD.Booking_ID
AND B.User_ID = U.User_ID
AND B.Work_Type_ID = WT.Work_Type_ID
AND B.Project_ID = P.Project_ID
AND P.Project_ID = 32
AND P.Project_ID = WT.Project_ID
GROUP BY Name, B.Booking_Type, Work_Type WITH ROLLUP;
The problem with adding unavailability to the query is that it is not
related to a project but work types are, also i can't work out how to do
percentages in mysql, would be most grateful for your advice. Here are the
relevant tables:
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 | |
| Pre_Event_Copy_Received_By_Scheduling | char(3) | YES | |
NULL | |
| Post_Event_Original_Completed_Form_Received | char(3) | YES | |
NULL | |
| Section_C | char(3) | YES | |
NULL | |
| Date_Difference | varchar(20) | | |
n/a | |
| AU_Booking_ID | int(11) | YES | |
NULL | |
| Original_Booking_ID | int(11) | YES | |
NULL | |
+---------------------------------------------+-------------+------+-----+---------------------+----------------+
23 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 |
|
| Rep_Viewable | char(3) | | | Yes |
|
| Administrator_ID | int(11) | YES | | NULL |
|
| Administrator_Phone_Number | varchar(20) | YES | | NULL |
|
| Project_Manager_ID_1 | int(11) | YES | | NULL |
|
| Project_Manager_ID_2 | int(11) | YES | | NULL |
|
+----------------------------+--------------+------+-----+---------+----------------+
8 rows in set (0.00 sec)
mysql> DESC Work_Types;
+--------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+----------------+
| Work_Type_ID | int(3) | | PRI | NULL | auto_increment |
| Project_ID | int(11) | YES | | NULL | |
| Day_Type | int(2) | YES | | NULL | |
| Work_Type | varchar(40) | | | | |
+--------------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
mysql> DESC Unavailability_Descriptions;
+-----------------------------------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key |
Default | Extra |
+-----------------------------------------+--------------+------+-----+---------+-------+
| Unavailability_ID | int(11) | | PRI | 0
| |
| Unavailability_Description | varchar(100) | YES | | NULL
| |
| Unavailability_Description_Abbreviation | char(3) | | |
| |
+-----------------------------------------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> DESC Booking_Dates;
+------------+---------+------+-----+------------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+---------+------+-----+------------+-------+
| Booking_ID | int(11) | | PRI | 0 | |
| Date | date | | PRI | 0000-00-00 | |
+------------+---------+------+-----+------------+-------+
2 rows in set (0.00 sec)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
