使用过滤条件查找任务类型(任务计数，错误计数)的计数

Question

我正在尝试使用过滤条件查找任务类型(任务计数，错误计数(的计数，但是当我编写此代码时，它忽略了过滤条件并从整个表中给了我计数。 请帮助我使用正确的代码。

SELECT A.[projectname], 
A.[projectid], 
A.[releasename], 
A.[releaseid], 
A.[sprintname], 
A.[sprintid], 
A.issuenumber, 
dt.tasktype, 
Sprint.sprintcompletedate date, 
CASE 
WHEN dt.tasktype = 'Task' THEN Count(a.tasktype) 
END                       taskcount, 
CASE 
WHEN dt.tasktype = 'Bug' THEN Count(a.tasktype) 
END                       Bugcount 
FROM   cqm_details AS A 
INNER JOIN cqm_details dt 
ON a.issuenumber = dt.parentkey 
INNER JOIN dbo.cqm_sprints Sprint 
ON Sprint.sprintid = A.sprintid 
WHERE  A.tasktype = 'Story' 
AND A.taskstatus IN ( 'Closed', 'Done', 'Completed' ) 
AND a.projectid = 106 
AND Sprint.sprintcompletedate BETWEEN '2020-05-01' AND '2020-05-30' 
GROUP  BY A.[projectname], 
A.[projectid], 
A.[releasename], 
A.[releaseid], 
A.[sprintname], 
A.[sprintid], 
A.issuenumber, 
dt.tasktype, 
Sprint.sprintcompletedate 

Answer 1

我试图复制提到的问题，但结果看起来不错。

请在此处找到带有示例数据的数据库<>小提琴。

如果结果不正常，请更改/添加示例数据并提供 db<>fiddle URL。

Answer 2

您是否正在尝试在单独的行上获取每个任务类型的计数？

SELECT A.[projectname], 
A.[projectid], 
A.[releasename], 
A.[releaseid], 
A.[sprintname], 
A.[sprintid], 
A.issuenumber, 
dt.tasktype, 
Sprint.sprintcompletedate date, 
SUM(CASE WHEN a.tasktype IN ('Task','Bug') THEN 1 ELSE 0 END) OVER(PARTITION BY a.tasktype) AS taskbugcount
FROM   cqm_details AS A 
INNER JOIN cqm_details dt 
ON a.issuenumber = dt.parentkey 
INNER JOIN dbo.cqm_sprints Sprint 
ON Sprint.sprintid = A.sprintid 
WHERE  A.tasktype = 'Story' 
AND A.taskstatus IN ( 'Closed', 'Done', 'Completed' ) 
AND a.projectid = 106 
AND Sprint.sprintcompletedate BETWEEN '2020-05-01' AND '2020-05-30'

还是针对该问题进行了分组？

SELECT A.[projectname], 
A.[projectid], 
A.[releasename], 
A.[releaseid], 
A.[sprintname], 
A.[sprintid], 
A.issuenumber, 
dt.tasktype, 
Sprint.sprintcompletedate date, 
SUM(CASE WHEN a.tasktype IN ('Task','Bug') THEN 1 ELSE 0 END) taskbugcount                    Bugcount 
FROM   cqm_details AS A 
INNER JOIN cqm_details dt 
ON a.issuenumber = dt.parentkey 
INNER JOIN dbo.cqm_sprints Sprint 
ON Sprint.sprintid = A.sprintid 
WHERE  A.tasktype = 'Story' 
AND A.taskstatus IN ( 'Closed', 'Done', 'Completed' ) 
AND a.projectid = 106 
AND Sprint.sprintcompletedate BETWEEN '2020-05-01' AND '2020-05-30' 
GROUP  BY A.[projectname], 
A.[projectid], 
A.[releasename], 
A.[releaseid], 
A.[sprintname], 
A.[sprintid], 
A.issuenumber, 
dt.tasktype, 
Sprint.sprintcompletedate 

如果您尝试为任务和 bug 获取单独的计数，只需复制逻辑并分别针对"任务"和"错误"检查任务类型。