MySQL ODBC Connector - A Complicated Report

Author

A Complicated Report

Shaun

2005-09-24, 7:23 am

Peter Brawley

2005-09-24, 8:23 pm

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_Descr
iptions. 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_descr
iptions 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_Descr
iptions. 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_Dat
e | 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_D
ate | 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_Recei
ved_By_Scheduling | char(3) | YES | |
>NULL | |
>| Post_Event_Original_
Completed_Form_Recei
ved | 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_Descr
iptions;
>+-----------------------------------------+--------------+------+-----+---------+-------+
>| Field | Type | Null | Key |
>Default | Extra |
>+-----------------------------------------+--------------+------+-----+---------+-------+
>| Unavailability_ID | int(11) | | PRI | 0
>| |
>| Unavailability_Descr
iption | varchar(100) | YES | | NULL
>| |
>| Unavailability_Descr
iption_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

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

Shaun

2005-09-26, 7:23 am

"Peter Brawley" <peter.brawley@earthlink.net> wrote in message
news:4335E0D0.5020206@earthlink.net...
> Shaun,
>
> days
>
>
> 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_descr
iptions 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:
>
>
>
> --
> 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_Descr
iption,
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_Descr
iptions AS UD USING(Unavailability
_ID)
WHERE P.Project_ID = 32
GROUP BY Name, B.Booking_Type, Work_Type, Unavailability_Descr
iption 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/mysql? unsub...sie.nctu.edu.tw

Sponsored Links