SQL-统计JIRA敏捷看板中的工作量

如果觉得这篇文章不错，请记得点赞、收藏和分享，也欢迎加我个人微信交流：craigyoung1985，请备注：姓名/城市/工作岗位。

– **************************************************** –
– 该脚本用来查询一个迭代中子任务的估算时间、已工作时间和剩余时间 –
– 作者：YY哥 (Email: craigyoung@live.cn) –
– 日期: 2017/2/20 –
– 适用场景：–
– 1.JIRA史诗故事(Epic)代表产品特性，即：大的用户估时或功能点，史诗故事被拆分为多个Stories(可独立开发、测试和发布的功能) –
– 2.JIRA故事(Story)代表产品功能点，故事被拆分为多个Technical Tasks(包含：开发任务和测试任务等） –
– 3.JIRA系统设置中开启了“时间跟踪”状态 –
– 4.JIRA敏捷看板配置中，预估->时间跟踪：剩余时间评估和花费时间（即：任务的时间追踪使用JIRA的剩余预估时间和已花费时间字段，燃尽图是以这两个字段为基础进行展开的）–
– 5.JIRA工作流：Open->In Progress->Resolved->Closed->Reopened->In Progress->… –
– *************************************************** –

#迭代名称
DECLARE @Sprint VARCHAR(100);
SET @Sprint = “XXX”;

#1.查询迭代中每个产品特性的剩余工作量等
SELECT epic.summary “产品特性Epic”,
SUM(CASE WHEN task.issuestatus IN (1,3,4) THEN task.timeestimate/3600
ELSE 0 END) AS “剩余工作量（小时）”,
SUM(task.timeoriginalestimate/3600) “总工作量（小时）”,
– 燃尽图累计工时的计算规则：1.技术任务为Resolved或Closed状态，使用：总工作量（原预估时间）；2.否则，当已工作时间大于总工作量时，使用：总工作量（原预估时间），小于则使用：已工作时间
SUM(CASE task.issuestatus
WHEN 5 THEN
IFNULL(task.timeoriginalestimate,0)
WHEN 6 THEN
IFNULL(task.timeoriginalestimate,0)
ELSE
IF(IFNULL(task.timespent,0) >= task.timeoriginalestimate, task.timeoriginalestimate, IFNULL(task.timespent,0))
END)/3600 AS “已完成工作量（小时）”,
– 完成率 = 已完成工作量/总工作量（原预估时间）
CONCAT(LEFT(SUM(CASE task.issuestatus
WHEN 5 THEN
IFNULL(task.timeoriginalestimate,0)
WHEN 6 THEN
IFNULL(task.timeoriginalestimate,0)
ELSE
IF(IFNULL(task.timespent,0) >= task.timeoriginalestimate, task.timeoriginalestimate, IFNULL(task.timespent,0))
END)/3600/SUM(task.timeoriginalestimate/3600) * 100, 4), ‘%’) “完成率%”,
SUM(IFNULL(task.timespent/3600,0)) “实际工作量（小时）”
FROM customfieldvalue AS c
LEFT OUTER JOIN jiraissue story ON story.ID = c.ISSUE
LEFT OUTER JOIN AO_60DB71_SPRINT sprint ON sprint.ID = c.STRINGVALUE
LEFT JOIN issuetype it ON it.id = story.issuetype
LEFT JOIN issuelink stlink ON stlink.source = story.id
LEFT JOIN jiraissue task ON task.id = stlink.destination
LEFT JOIN issuetype itd ON itd.id = task.issuetype
LEFT JOIN issuelink eslink ON eslink.destination = story.id
LEFT JOIN jiraissue epic ON epic.id = eslink.source
WHERE stlink.linktype = 10100 AND eslink.linktype = 10200 AND CUSTOMFIELD = 10005
AND sprint.NAME = @Sprint
GROUP BY epic.issuenum
ORDER BY SUM(task.timeestimate/3600) DESC;

#2.查询迭代中每个经办人的剩余工作量等
SELECT task.ASSIGNEE “经办人”,
SUM(CASE WHEN task.issuestatus IN (1,3,4) THEN task.timeestimate/3600
ELSE 0 END) AS “剩余工作量（小时）”,
SUM(task.timeoriginalestimate/3600) “总工作量（小时）”,
SUM(CASE task.issuestatus
WHEN 5 THEN
IFNULL(task.timeoriginalestimate,0)
WHEN 6 THEN
IFNULL(task.timeoriginalestimate,0)
ELSE
IF(IFNULL(task.timespent,0) >= task.timeoriginalestimate, task.timeoriginalestimate, IFNULL(task.timespent,0))
END)/3600 AS “已完成工作量（小时）”,
SUM(IFNULL(task.timespent/3600,0)) “实际工作量（小时）”
FROM customfieldvalue AS c
LEFT OUTER JOIN jiraissue story ON story.ID = c.ISSUE
LEFT OUTER JOIN AO_60DB71_SPRINT sprint ON sprint.ID = c.STRINGVALUE
LEFT JOIN issuetype it ON it.id = story.issuetype
LEFT JOIN issuelink stlink ON stlink.source = story.id
LEFT JOIN jiraissue task ON task.id = stlink.destination
LEFT JOIN issuetype itd ON itd.id = task.issuetype
LEFT JOIN issuelink eslink ON eslink.destination = story.id
LEFT JOIN jiraissue epic ON epic.id = eslink.source
WHERE stlink.linktype = 10100 AND eslink.linktype = 10200
AND sprint.NAME = @Sprint
GROUP BY task.assignee
ORDER BY SUM(task.timeestimate/3600) DESC;

#3.查询迭代中每个经办人对应的产品特性的剩余工作量等
SELECT epic.summary “产品特性Epic”,
task.ASSIGNEE “经办人”,
SUM(CASE WHEN task.issuestatus IN (1,3,4) THEN task.timeestimate/3600
ELSE 0 END) AS “剩余工作量（小时）”,
SUM(task.timeoriginalestimate/3600) “总工作量（小时）”,
SUM(CASE task.issuestatus
WHEN 5 THEN
IFNULL(task.timeoriginalestimate,0)
WHEN 6 THEN
IFNULL(task.timeoriginalestimate,0)
ELSE
IF(IFNULL(task.timespent,0) >= task.timeoriginalestimate, task.timeoriginalestimate, IFNULL(task.timespent,0))
END)/3600 AS “已完成工作量（小时）”,
SUM(IFNULL(task.timespent/3600,0)) “实际工作量（小时）”
FROM customfieldvalue AS c
LEFT OUTER JOIN jiraissue story ON story.ID = c.ISSUE
LEFT OUTER JOIN AO_60DB71_SPRINT sprint ON sprint.ID = c.STRINGVALUE
LEFT JOIN issuetype it ON it.id = story.issuetype
LEFT JOIN issuelink stlink ON stlink.source = story.id
LEFT JOIN jiraissue task ON task.id = stlink.destination
LEFT JOIN issuetype itd ON itd.id = task.issuetype
LEFT JOIN issuelink eslink ON eslink.destination = story.id
LEFT JOIN jiraissue epic ON epic.id = eslink.source
WHERE stlink.linktype = 10100 AND eslink.linktype = 10200
AND sprint.NAME = @Sprint
GROUP BY epic.summary, task.assignee
ORDER BY epic.summary, SUM(task.timeestimate/3600) DESC, task.assignee;

#4.查询迭代中任务工作量详情：产品特性Epic->故事Story->技术任务Technical Tasks
SELECT epic.issuenum “Epic Number”, epic.summary “产品特性Epic”,
story.issuenum “Story Number”, story.summary “故事”, it.pname,
task.issuenum “Technical Task Number”, task.summary “技术任务”, itd.pname,
isd.pname “任务状态”, task.ASSIGNEE “经办人”,
task.timeestimate/3600 “剩余工作量（小时）”, task.timeoriginalestimate/3600 “总工作量（小时）”, task.timespent/3600 “实际工作量（小时）”
FROM customfieldvalue AS c
LEFT OUTER JOIN jiraissue story ON story.ID = c.ISSUE
LEFT OUTER JOIN AO_60DB71_SPRINT sprint ON sprint.ID = c.STRINGVALUE
LEFT JOIN issuetype it ON it.id = story.issuetype
LEFT JOIN issuelink stlink ON stlink.source = story.id
LEFT JOIN jiraissue task ON task.id = stlink.destination
LEFT JOIN issuetype itd ON itd.id = task.issuetype
LEFT JOIN issuelink eslink ON eslink.destination = story.id
LEFT JOIN jiraissue epic ON epic.id = eslink.source
LEFT JOIN issuestatus isd ON task.issuestatus = isd.id
WHERE stlink.linktype = 10100 AND eslink.linktype = 10200
AND sprint.NAME = @Sprint
#AND task.ASSIGNEE = “peizilong”
ORDER BY task.ASSIGNEE, isd.id, epic.issuenum, story.issuenum, task.issuenum;

– **************************************************** –
– 5.当故事Story没继续拆分成技术任务时，查询迭代中每个产品特性的剩余工作量等 –
– **************************************************** –
SELECT epic.summary “产品特性Epic”,
SUM(CASE WHEN story.issuestatus IN (1,3,4) THEN story.timeestimate/3600
ELSE 0 END) AS “剩余工作量（小时）”,
SUM(story.timeoriginalestimate/3600) “总工作量（小时）”,
SUM(CASE story.issuestatus
WHEN 5 THEN
IFNULL(story.timeoriginalestimate,0)
WHEN 6 THEN
IFNULL(story.timeoriginalestimate,0)
ELSE
IF(IFNULL(story.timespent,0) >= story.timeoriginalestimate, story.timeoriginalestimate, IFNULL(story.timespent,0))
END)/3600 AS “已完成工作量（小时）”,
CONCAT(LEFT(SUM(CASE story.issuestatus
WHEN 5 THEN
IFNULL(story.timeoriginalestimate,0)
WHEN 6 THEN
IFNULL(story.timeoriginalestimate,0)
ELSE
IF(IFNULL(story.timespent,0) >= story.timeoriginalestimate, story.timeoriginalestimate, IFNULL(story.timespent,0))
END)/3600/SUM(story.timeoriginalestimate/3600) * 100, 4), ‘%’) “完成率%”,
SUM(IFNULL(story.timespent/3600,0)) “实际工作量（小时）”
FROM customfieldvalue AS c
LEFT OUTER JOIN jiraissue story ON story.ID = c.ISSUE
LEFT OUTER JOIN AO_60DB71_SPRINT sprint ON sprint.ID = c.STRINGVALUE
LEFT JOIN issuetype it ON it.id = story.issuetype
LEFT JOIN issuelink stlink ON stlink.source = story.id
LEFT JOIN issuelink eslink ON eslink.destination = story.id
LEFT JOIN jiraissue epic ON epic.id = eslink.source
WHERE eslink.linktype = 10200 AND CUSTOMFIELD = 10005
AND sprint.NAME = @Sprint
GROUP BY epic.issuenum;

– **************************************************** –
– 6.当故事Story没继续拆分成技术任务时，查询迭代中每个经办人的剩余工作量等 –
– **************************************************** –
SELECT story.ASSIGNEE “经办人”,
SUM(CASE WHEN story.issuestatus IN (1,3,4) THEN story.timeestimate/3600
ELSE 0 END) AS “剩余工作量（小时）”,
SUM(story.timeoriginalestimate/3600) “总工作量（小时）”,
SUM(CASE story.issuestatus
WHEN 5 THEN
IFNULL(story.timeoriginalestimate,0)
WHEN 6 THEN
IFNULL(story.timeoriginalestimate,0)
ELSE
IF(IFNULL(story.timespent,0) >= story.timeoriginalestimate, story.timeoriginalestimate, IFNULL(story.timespent,0))
END)/3600 AS “已完成工作量（小时）”,
SUM(IFNULL(story.timespent/3600,0)) “实际工作量（小时）”
FROM customfieldvalue AS c
LEFT OUTER JOIN jiraissue story ON story.ID = c.ISSUE
LEFT OUTER JOIN AO_60DB71_SPRINT sprint ON sprint.ID = c.STRINGVALUE
LEFT JOIN issuetype it ON it.id = story.issuetype
LEFT JOIN issuelink stlink ON stlink.source = story.id
LEFT JOIN issuelink eslink ON eslink.destination = story.id
LEFT JOIN jiraissue epic ON epic.id = eslink.source
WHERE eslink.linktype = 10200
AND sprint.NAME = @Sprint
GROUP BY story.assignee
ORDER BY SUM(story.timeoriginalestimate/3600) DESC;

欢迎加我个人微信交流 craigyoung1985，一起学习和交流。请备注：姓名/城市/工作岗位，我可以拉到Jira和Confluence中国社区群。