List:General Discussion« Previous MessageNext Message »
From:Shaun Date:September 24 2005 11:08am
Subject:A Complicated Report
View as plain text  
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)



Thread
A Complicated ReportShaun24 Sep
  • Re: A Complicated ReportPeter Brawley25 Sep
  • Re: A Complicated ReportShaun26 Sep