List:General Discussion« Previous MessageNext Message »
From:Peter Brawley Date:September 24 2005 11:27pm
Subject:Re: A Complicated Report
View as plain text  
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

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