Jira统计工时sql备份

淩亂°似流年 2024-04-18 22:20 68阅读 0赞

1、工时汇总

SELECT pro.pname as 项目名称,gro.group\_name as 组名称,ROUND(SUM(b.NUMBERVALUE),1) as 工时 FROM jiraissue a  
LEFT JOIN customfieldvalue b on a.id=b.ISSUE and b.customfield=10600  
LEFT JOIN customfieldvalue c on a.id=c.ISSUE and c.customfield=10700  
LEFT JOIN app\_user au on a.CREATOR=au.user\_key  
LEFT JOIN cwd\_user usr on au.lower\_user\_name=usr.lower\_user\_name  
LEFT JOIN cwd\_membership mem on  mem.child\_id=usr.ID  
LEFT JOIN cwd\_group gro on mem.parent\_id=gro.ID  
LEFT JOIN project pro on a.project=pro.ID  
WHERE  
\-- usr.active=1  
\-- AND gro.group\_name='jira-developers'  
\-- AND a.PROJECT=11946  
\-- pro.pname in('')  
\-- AND  
a.issuetype=3   
AND c.DATEVALUE is not null  
AND c.DATEVALUE BETWEEN '2019-08-01 00:00:00' AND '2019-08-31 00:00:00'  
AND gro.group\_name in('')  
GROUP BY pro.ID,gro.group\_name;

2、工时明细

\-- 人员姓名、工时、任务执行时间  
SELECT pro.pname as 项目名称,gro.group\_name as 组名称,usr.display\_name as 人员,ROUND(b.NUMBERVALUE,1) as 工时,c.DATEVALUE as 任务执行时间 FROM jiraissue a  
LEFT JOIN customfieldvalue b on a.id=b.ISSUE and b.customfield=10600  
LEFT JOIN customfieldvalue c on a.id=c.ISSUE and c.customfield=10700  
LEFT JOIN app\_user au on a.CREATOR=au.user\_key  
LEFT JOIN cwd\_user usr on au.lower\_user\_name=usr.lower\_user\_name  
LEFT JOIN cwd\_membership mem on  mem.child\_id=usr.ID  
LEFT JOIN cwd\_group gro on mem.parent\_id=gro.ID  
LEFT JOIN project pro on a.project=pro.ID  
WHERE  
\-- usr.active=1  
\-- AND gro.group\_name='jira-developers'  
\-- AND a.PROJECT=11946  
\-- pro.pname in('市')  
\-- AND  
a.issuetype=3   
AND c.DATEVALUE is not null  
AND c.DATEVALUE BETWEEN '2019-07-01 00:00:00' AND '2019-07-31 00:00:00'  
AND gro.group\_name in('')  
ORDER BY pro.ID,gro.group\_name,usr.display\_name,c.DATEVALUE;