SQL-查询 Jira 中已创建与已解决问题对比报告

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

DECLARE @DaysLapse INT;
DECLARE @ProjectKey VARCHAR(255);

SET @DaysLapse = 6;
SET @ProjectKey = ‘XXX’;

SELECT DATE(ji.CREATED) AS YearMonth,
COUNT(ji.CREATED) AS CreatedTicketNo,
T.ResolvedTciektNo AS ResolvedTicketNo
FROM jiraissue ji
INNER JOIN project pr ON pr.ID = ji.PROJECT
LEFT JOIN (
SELECT
DATE(ji.RESOLUTIONDATE) AS YearMonth,
COUNT(ji.RESOLUTIONDATE) AS ResolvedTciektNo
FROM jiraissue ji
INNER JOIN project pr ON pr.ID = ji.PROJECT
WHERE pr.pkey = @ProjectKey
AND ji.RESOLUTIONDATE >= DATE_SUB(DATE(NOW()),INTERVAL @DaysLapse DAY)
GROUP BY DATE(ji.RESOLUTIONDATE)
) T ON T.YearMonth = DATE(ji.CREATED)
WHERE pr.pkey = @ProjectKey
AND ji.CREATED >= DATE_SUB(DATE(NOW()),INTERVAL @DaysLapse DAY)
GROUP BY DATE(ji.CREATED);

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