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
