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

-- **************************************************** ---- 该脚本用来查询一个迭代中子任务的估算时间、已工作时间和剩余时间 ---- 作者：YY哥 (Email: [e-mail]) ---- 日期: 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.sourceWHERE stlink.linktype = 10100 AND eslink.linktype = 10200 AND CUSTOMFIELD = 10005     AND sprint.`NAME` = @SprintGROUP BY epic.issuenumORDER 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.sourceWHERE stlink.linktype = 10100 AND eslink.linktype = 10200    AND sprint.`NAME` = @SprintGROUP BY task.assigneeORDER 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.sourceWHERE stlink.linktype = 10100 AND eslink.linktype = 10200    AND sprint.`NAME` = @SprintGROUP BY epic.summary, task.assigneeORDER BY epic.summary, SUM(task.timeestimate/3600) DESC, task.assignee;#4.查询迭代中任务工作量详情：产品特性Epic->故事Story->技术任务Technical TasksSELECT 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.idWHERE 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.sourceWHERE eslink.linktype = 10200 AND CUSTOMFIELD = 10005     AND sprint.`NAME` = @SprintGROUP 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.sourceWHERE eslink.linktype = 10200    AND sprint.`NAME` = @SprintGROUP BY story.assigneeORDER BY SUM(story.timeoriginalestimate/3600) DESC;