From: Peter Brawley Date: September 24 2005 11:27pm Subject: Re: A Complicated Report List-Archive: http://lists.mysql.com/mysql/189515 Message-Id: <4335E0D0.5020206@earthlink.net> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit 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