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
分享到:
相关推荐
后端拿到请求参数需要进行sql语句拼接,由于mybatis-generator的example类存在较多问题,对其进行改进: 1.实现生成的example文件代码量大量减少 2.保证业务代码中不出现sql语句 3.可以生成嵌套的or条件语句 4....
Winterleaf-WebDavSharp.SQLExample #A具有权限等的基于SQL Server的Web DAV服务器 !!!!!!!!重要的!!!!!!! 此应用程序仅适用于64位模式。 !!!!!!!!重要的!!!!!!! 对于本软件,我们...
Proc SQL by Example, Using SQL within SAS
英文版Oracle PL/SQL by Example(4th Edition)
Oracle® PL/SQL™ by Example, Third Edition By Benjamin Rosenzweig, Elena Silvestrova Publisher : Prentice Hall PTR Pub Date : September 10, 2003 ISBN : 0-13-117261-1 Pages : 768
An introduction to SQL for the absolute beginner. Learn how to create and query databases, add and remove data from tables, set constraints, create stored procedures, and more
SQL表明明有数据,为何SqlDataReader reader = command.ExecuteReader();reader.HasRow返回false? 论坛问题C#代码
一本学习ORACLE PL/SQL不可多得好书! 英语原版,高清晰。 多多学习,多多交流!
Oracle SQL By Example 4th Edition by Alice Rischert - 5 Star Book Review.pdf
SQL_EXAMPLES SQL指南 这实际上是常用SQL查询的列表。 最适合在需要刷新或学习查询时用作参考材料。 只需打开文件2 _...,然后使用CTRL + F(或任何可用的搜索)并在查询中搜索关键字。 第一份文档只是SQL的含义,...
PL.SQL.By.Example,是oracle数据库plsql编程的实例脚本,值得一看。
An introduction to SQL for the absolute beginner. Learn how to create and query databases, add and remove data from tables, set constraints, create stored procedures, and more
一个SQL Server数据库操作通用类example_ DBHelper
支持范例文件管理(SQL Example Manager) 支持拖放数据库加载(DragDrop Database) 结果输出(Export to CSV/XML/TXT) 多国语言显示(Multiple Language): 预设中英文,并可自行扩充 结果打印(Print query ...
英文原版 Start developing applications with Oracle PL/SQL
Oracle PL_SQL by Example,PL_SQL 案例在用jdbcs时可以参考
There are a lot of materials mention the hr_main.sql for test, but I can't find it in the $ORACLE_HOME/demo/schema/human_resources/; so enjoy it for you and wish to help U
实例学pl/sql,很独到,很实用,配套资源可上http://www.informit.com/store/product.aspx?isbn=0137144229下载,好好练两遍,pl/sql就成了
Camel CSV 2 SQL 示例