"Peter Brawley" <[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
> 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...
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
