SQL example

SELECT items.Name AS Name ,
Work_Items AS '#no Workitems',
sec_to_time(Project_Time) AS 'Client/Project Total Time',
sec_to_time(Ticket_project_Time) AS 'Client/Project Time (With Ticket)',
sec_to_time(Total_Hours) AS 'Total Time (not inc breaks)',
Research_Time AS 'Research/Mentoring',
sec_to_time(if(Ticket_project_Time ,
(Project_Time-Ticket_project_Time),Project_Time)) AS 'Client/Project Time (No Ticket)',
sec_to_time(Others_time) AS 'Other' ,
sec_to_time(un_allocated.un_allocated_time) AS 'Un Allocated time'
FROM
(SELECT
count(*) AS Work_Items ,
per.surname AS Name
FROM T4U_WORKITEMS wi
INNER JOIN T4U_DAYINFOS di ON ( wi.dayinfo_id = di.id )
INNER JOIN T4U_TASKS t ON ( wi.task_id = t.id )
INNER JOIN T4U_PROJECTS p ON ( wi.project_id = p.id )
INNER JOIN T4U_PERSONS per ON ( di.person_id = per.id )
WHERE di.daydate BETWEEN '2009-02-10' AND '2009-02-29'
AND lower(per.surname) LIKE 'eric%'
GROUP BY per.surName) AS items

LEFT JOIN
(
SELECT per.surname AS Name ,
sum(wi.wEnd-wi.wBegin) AS Project_Time
FROM T4U_WORKITEMS wi
INNER JOIN T4U_DAYINFOS di ON ( wi.dayinfo_id = di.id )
INNER JOIN T4U_TASKS t ON ( wi.task_id = t.id )
INNER JOIN T4U_PROJECTS p ON ( wi.project_id = p.id )
INNER JOIN T4U_PERSONS per ON ( di.person_id = per.id )
INNER JOIN T4U_PROJECTS parent ON ( ifnull(p.parent_id , p.id) = parent.id )
INNER JOIN T4U_PROJECTS grandfather ON ( ifnull(parent.parent_id , parent.id) = grandfather.id )
INNER JOIN T4U_PROJECTS great ON ( ifnull(grandfather.parent_id , grandfather.id) = great.id )
INNER JOIN T4U_PROJECTS root ON ( ifnull(great.parent_id , great.id) = root.id )
WHERE
di.daydate BETWEEN '2009-02-10' AND '2009-02-29'
AND lower(root.name) = 'projects'
OR lower(root.name) = 'clients'
AND lower(per.surname) LIKE 'eric%'
GROUP BY per.surName) AS project

ON items.Name = project.Name

LEFT JOIN

(SELECT per.surname AS Name ,
t.name AS Task,
sum(wi.wEnd-wi.wBegin) AS Ticket_project_Time
FROM T4U_WORKITEMS wi
INNER JOIN T4U_DAYINFOS di ON ( wi.dayinfo_id = di.id )
INNER JOIN T4U_TASKS t ON ( wi.task_id = t.id )
INNER JOIN T4U_PROJECTS p ON ( wi.project_id = p.id )
INNER JOIN T4U_PERSONS per ON ( di.person_id = per.id )
INNER JOIN T4U_PROJECTS parent ON ( ifnull(p.parent_id , p.id) = parent.id )
INNER JOIN T4U_PROJECTS grandfather ON ( ifnull(parent.parent_id , parent.id) = grandfather.id )
INNER JOIN T4U_PROJECTS great ON ( ifnull(grandfather.parent_id , grandfather.id) = great.id )
INNER JOIN T4U_PROJECTS root ON ( ifnull(great.parent_id , great.id) = root.id )
WHERE
di.daydate BETWEEN '2009-02-10' AND '2009-02-29'
AND t.name LIKE "#%"
AND lower(root.name) = 'projects'
AND lower(per.surname) LIKE 'eric%'
OR lower(root.name) = 'clients'
GROUP BY per.surName) AS ticket

ON items.Name = ticket.Name

LEFT JOIN

(SELECT
sum(wi.wEnd-wi.wBegin) AS Total_Hours,
per.surname AS Name
FROM T4U_WORKITEMS wi
INNER JOIN T4U_DAYINFOS di ON ( wi.dayinfo_id = di.id )
INNER JOIN T4U_TASKS t ON ( wi.task_id = t.id )
INNER JOIN T4U_PROJECTS p ON ( wi.project_id = p.id )
INNER JOIN T4U_PERSONS per ON ( di.person_id = per.id )
INNER JOIN T4U_PROJECTS parent ON ( ifnull(p.parent_id , p.id) = parent.id )
INNER JOIN T4U_PROJECTS grandfather ON ( ifnull(parent.parent_id , parent.id) = grandfather.id )
INNER JOIN T4U_PROJECTS great ON ( ifnull(grandfather.parent_id , grandfather.id) = great.id )
INNER JOIN T4U_PROJECTS root ON ( ifnull(great.parent_id , great.id) = root.id )
WHERE di.daydate BETWEEN '2009-02-10' AND '2009-02-29'
AND lower(root.name) <> 'breaks'
AND lower(per.surname) LIKE 'eric%'
GROUP BY per.surName) AS totaltime

ON items.Name = totaltime.Name

LEFT JOIN

(
SELECT per.surname AS Name ,
sec_to_time(sum(wi.wEnd-wi.wBegin)) AS Research_Time
FROM T4U_WORKITEMS wi
INNER JOIN T4U_DAYINFOS di ON ( wi.dayinfo_id = di.id )
INNER JOIN T4U_TASKS t ON ( wi.task_id = t.id )
INNER JOIN T4U_PROJECTS p ON ( wi.project_id = p.id )
INNER JOIN T4U_PERSONS per ON ( di.person_id = per.id )
INNER JOIN T4U_PROJECTS parent ON ( ifnull(p.parent_id , p.id) = parent.id )
INNER JOIN T4U_PROJECTS grandfather ON ( ifnull(parent.parent_id , parent.id) = grandfather.id )
INNER JOIN T4U_PROJECTS great ON ( ifnull(grandfather.parent_id , grandfather.id) = great.id )
INNER JOIN T4U_PROJECTS root ON ( ifnull(great.parent_id , great.id) = root.id )
WHERE
di.daydate BETWEEN '2009-02-10' AND '2009-02-29'
AND lower(root.name) = 'research'
AND lower(per.surname) LIKE 'eric%'
GROUP BY per.surName) AS research

ON research.Name = items.Name

LEFT JOIN

(
SELECT per.surname AS Name ,
sec_to_time(sum(wi.wEnd-wi.wBegin)) AS Others_Time
FROM T4U_WORKITEMS wi
INNER JOIN T4U_DAYINFOS di ON ( wi.dayinfo_id = di.id )
INNER JOIN T4U_TASKS t ON ( wi.task_id = t.id )
INNER JOIN T4U_PROJECTS p ON ( wi.project_id = p.id )
INNER JOIN T4U_PERSONS per ON ( di.person_id = per.id )
INNER JOIN T4U_PROJECTS parent ON ( ifnull(p.parent_id , p.id) = parent.id )
INNER JOIN T4U_PROJECTS grandfather ON ( ifnull(parent.parent_id , parent.id) = grandfather.id )
INNER JOIN T4U_PROJECTS great ON ( ifnull(grandfather.parent_id , grandfather.id) = great.id )
INNER JOIN T4U_PROJECTS root ON ( ifnull(great.parent_id , great.id) = root.id )
WHERE
di.daydate BETWEEN '2009-02-10' AND '2009-02-29'
AND lower(root.name) <> 'research'
AND lower(root.name) <> 'projects'
AND lower(root.name) <> 'clients'
AND lower(root.name) <> 'breaks'
AND lower(per.surname) LIKE 'eric%'
GROUP BY per.surName) AS others

ON others.Name = items.Name

LEFT JOIN
(
SELECT per.surname AS Name ,
(max(wi.wEnd)-min(wi.wBegin)
) - sum(wi.wEnd-wi.wBegin) AS un_allocated_time
FROM T4U_WORKITEMS wi
INNER JOIN T4U_DAYINFOS di ON ( wi.dayinfo_id = di.id )
INNER JOIN T4U_TASKS t ON ( wi.task_id = t.id )
INNER JOIN T4U_PROJECTS p ON ( wi.project_id = p.id )
INNER JOIN T4U_PERSONS per ON ( di.person_id = per.id )
INNER JOIN T4U_PROJECTS parent ON ( ifnull(p.parent_id , p.id) = parent.id )

WHERE
di.daydate BETWEEN '2009-02-10' AND '2009-02-29'
AND per.surName LIKE "eric%"

GROUP BY per.surname ) AS un_allocated

ON un_allocated.Name = items.Name