Shaun,
>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%
First, your query's easier to work with when its JOINs are explicit:
SELECT
CONCAT_WS(' ', U.User_Firstname, U.User_Lastname) AS Name,
B.Booking_Type,
WT.Work_Type,
SUM(IF(MONTHNAME(BD.Date) = 'January' 1, 0)) AS 'January',
SUM(IF(MONTHNAME(BD.Date) = 'February'', 1, 0)) AS 'February',
SUM(IF(MONTHNAME(BD.Date) = 'March', 1, 0)) AS 'March',
SUM(IF(MONTHNAME(BD.Date) = 'April', 1, 0)) AS 'April',
SUM(IF(MONTHNAME(BD.Date) = 'May', 1, 0)) AS 'May',
SUM(IF(MONTHNAME(BD.Date) = 'June', 1, 0)) AS 'June',
SUM(IF(MONTHNAME(BD.Date) = 'July', 1, 0)) AS 'July',
SUM(IF(MONTHNAME(BD.Date) = 'August', 1, 0)) AS 'August',
SUM(IF(MONTHNAME(BD.Date) = 'September', 1, 0)) AS 'September',
SUM(IF(MONTHNAME(BD.Date) = 'October', 1, 0)) AS 'October',
SUM(IF(MONTHNAME(BD.Date) = 'November', 1, 0)) AS 'November',
SUM(IF(MONTHNAME(BD.Date) = 'December', 1, 0)) AS 'December'
FROM Bookings AS B,
INNER JOIN Users AS U USING(User_ID)
INNER JOIN Booking_Dates AS BD USING(Booking_ID),
INNER JOIN Work_Types AS WT USING(Work_Type_Id)
INNER JOIN Projects AS P USING(Project_ID)
WHERE YEAR(BD.Date) = 2005
AND P.Project_ID = 32
GROUP BY
Name,
B.Booking_Type,
Work_Type
WITH ROLLUP;
Now, you say unavailability data does not relate to projects, but
Bookings columns user_id, project_id and unavailability_id encode just
such a relationship, don't they? Supposing that's so, it seems to me you
could add a join like
...
FROM Bookings AS B
...
LEFT JOIN unavailability_descriptions USING(unavailability_id)
...
and add SELECTs which sum the result of ISNULL() on the unavailability
data, or whatever other computation you need.
PB
http://www.artfulsoftware.com
-----
Shaun wrote:
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)
--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.344 / Virus Database: 267.11.5/110 - Release Date: 9/22/2005
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
