List:General Discussion« Previous MessageNext Message »
From:Shaun Date:September 26 2005 8:47am
Subject:Re: A Complicated Report
View as plain text  
"Peter Brawley" <peter.brawley@stripped> wrote in message 
news:4335E0D0.5020206@ style="color:#666">stripped...
> 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
>

Hi Peter,

Thanks for your reply, I am having some trouble with the inner joins - it 
appears to be attmpting to join to the previous join table rather than 
Bookings:

SELECT
CONCAT_WS(' ', U.User_Firstname, U.User_Lastname) AS Name,
B.Booking_Type,
WT.Work_Type,
UD.Unavailability_Description,
SUM(IF(MONTHNAME(BD.Date) = 'January' AND Year(BD.Date) = '2005' AND 
B.Booking_Type = 'Booking', 1, 0)) AS 'Days worked in January',
SUM(IF(MONTHNAME(BD.Date) = 'January' AND Year(BD.Date) = '2005' AND 
B.Booking_Type = 'Unavailability', 1, 0)) AS 'Days off in January',
SUM(IF(MONTHNAME(BD.Date) = 'February' AND Year(BD.Date) = '2005' AND 
B.Booking_Type = 'Booking', 1, 0)) AS 'Days worked in February',
SUM(IF(MONTHNAME(BD.Date) = 'February' AND Year(BD.Date) = '2005' AND 
B.Booking_Type = 'Unavailability', 1, 0)) AS 'Days off in February',
SUM(IF(MONTHNAME(BD.Date) = 'March' AND Year(BD.Date) = '2005' AND 
B.Booking_Type = 'Booking', 1, 0)) AS 'Days worked in March',
SUM(IF(MONTHNAME(BD.Date) = 'March' AND Year(BD.Date) = '2005' AND 
B.Booking_Type = 'Unavailability', 1, 0)) AS 'Days off in March',
SUM(IF(MONTHNAME(BD.Date) = 'April' AND Year(BD.Date) = '2005' AND 
B.Booking_Type = 'Booking', 1, 0)) AS 'Days worked in April',
SUM(IF(MONTHNAME(BD.Date) = 'April' AND Year(BD.Date) = '2005' AND 
B.Booking_Type = 'Unavailability', 1, 0)) AS 'Days off in April',
SUM(IF(MONTHNAME(BD.Date) = 'May' AND Year(BD.Date) = '2005' AND 
B.Booking_Type = 'Booking', 1, 0)) AS 'Days worked in May',
SUM(IF(MONTHNAME(BD.Date) = 'May' AND Year(BD.Date) = '2005' AND 
B.Booking_Type = 'Unavailability', 1, 0)) AS 'Days off in May',
SUM(IF(MONTHNAME(BD.Date) = 'June' AND Year(BD.Date) = '2005' AND 
B.Booking_Type = 'Booking', 1, 0)) AS 'Days worked in June',
SUM(IF(MONTHNAME(BD.Date) = 'June' AND Year(BD.Date) = '2005' AND 
B.Booking_Type = 'Unavailability', 1, 0)) AS 'Days off in June',
SUM(IF(MONTHNAME(BD.Date) = 'July' AND Year(BD.Date) = '2005' AND 
B.Booking_Type = 'Booking', 1, 0)) AS 'Days worked in July',
SUM(IF(MONTHNAME(BD.Date) = 'July' AND Year(BD.Date) = '2005' AND 
B.Booking_Type = 'Unavailability', 1, 0)) AS 'Days off in July',
SUM(IF(MONTHNAME(BD.Date) = 'August' AND Year(BD.Date) = '2005' AND 
B.Booking_Type = 'Booking', 1, 0)) AS 'Days worked in August',
SUM(IF(MONTHNAME(BD.Date) = 'August' AND Year(BD.Date) = '2005' AND 
B.Booking_Type = 'Unavailability', 1, 0)) AS 'Days off in August',
SUM(IF(MONTHNAME(BD.Date) = 'September' AND Year(BD.Date) = '2005' AND 
B.Booking_Type = 'Booking', 1, 0)) AS 'Days worked in September',
SUM(IF(MONTHNAME(BD.Date) = 'September' AND Year(BD.Date) = '2005' AND 
B.Booking_Type = 'Unavailability', 1, 0)) AS 'Days off in September',
SUM(IF(MONTHNAME(BD.Date) = 'October' AND Year(BD.Date) = '2005' AND 
B.Booking_Type = 'Booking', 1, 0)) AS 'Days worked in October',
SUM(IF(MONTHNAME(BD.Date) = 'October' AND Year(BD.Date) = '2005' AND 
B.Booking_Type = 'Unavailability', 1, 0)) AS 'Days off in October',
SUM(IF(MONTHNAME(BD.Date) = 'November' AND Year(BD.Date) = '2005' AND 
B.Booking_Type = 'Booking', 1, 0)) AS 'Days worked in November',
SUM(IF(MONTHNAME(BD.Date) = 'November' AND Year(BD.Date) = '2005' AND 
B.Booking_Type = 'Unavailability', 1, 0)) AS 'Days off in November',
SUM(IF(MONTHNAME(BD.Date) = 'December' AND Year(BD.Date) = '2005' AND 
B.Booking_Type = 'Booking', 1, 0)) AS 'Days worked in December',
SUM(IF(MONTHNAME(BD.Date) = 'December' AND Year(BD.Date) = '2005' AND 
B.Booking_Type = 'Unavailability', 1, 0)) AS 'Days off in December'
FROM Bookings AS B
INNER JOIN Users AS U USING(User_ID)
INNER JOIN Projects AS P USING(Project_ID)
INNER JOIN Booking_Dates AS BD USING(Booking_ID)
INNER JOIN Work_Types AS WT USING(Work_Type_ID)
LEFT JOIN Unavailability_Descriptions AS UD USING(Unavailability_ID)
WHERE P.Project_ID = 32
GROUP BY Name, B.Booking_Type, Work_Type, Unavailability_Description WITH 
ROLLUP;

Unknown column 'workmanagement.U.Project_ID' in 'on clause'

Also if I inner join projects I wont get the unavailability descriptions as 
they dont relate to any projects... 


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