Jira统计工时sql备份

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;