不是为了唤起一个旧的帖子,但是简而言之:我遇到了类似的问题,并在我的Github上发布了一个解决方案。
如果你的每一列都有自己独特的状态,维托提供的解决方案对你来说是可行的,然而它只能告诉你工作项在当前状态下花费了多长时间,基于今天。它无法展示工作项在看板上的历史视图。
我提供的解决方案并不像一个简单的Power Shell脚本那样只给你报告,它是一个捕捉工作项所在看板列的历史变化的过程,允许你运行类似下面展示的报告。
SELECT DISTINCT
wi.WitID,
DATEDIFF(day, wic_new.AuditCreatedDate, Coalesce(wic_prog.AuditCreatedDate, wic_released.AuditCreatedDate, GETDATE())) as DaysInNew,
DATEDIFF(day, wic_prog.AuditCreatedDate, Coalesce(wic_released.AuditCreatedDate, GETDATE())) as DaysInProgess,
DATEDIFF(day, wic_released.AuditCreatedDate, GETDATE()) as DaysInReleased
FROM WorkItems wi
LEFT JOIN WorkItem_ColumnHistory wic_new on (wic_new.WitID = wi.WitID AND wic_new.CurrentColumn = 'New' AND wic_new.WefColumnID LIKE '%[YOUR TEAMS WEF ID]%')
LEFT JOIN WorkItem_ColumnHistory wic_prog on (wic_prog.WitID = wi.WitID AND wic_prog.CurrentColumn = 'In Progress' AND wic_prog.WefColumnID LIKE '%[YOUR TEAMS WEF ID]%')
LEFT JOIN WorkItem_ColumnHistory wic_released on (wic_released.WitID = wi.WitID AND wic_released.CurrentColumn = 'Released' AND wic_released.WefColumnID LIKE '%[YOUR TEAMS WEF ID]%')
WHERE
wi.[State] NOT IN('Closed', 'Removed')
ORDER BY WitID ASC
这个查询的解释已在链接的 GitHub 存储库的 ReadMe 文件中,因此我不会在此重复解释。基本上,此查询查看从 Azure DevOps 收集的历史数据,然后显示结果集,显示一个工作项在您的看板板的每个列中存在多长时间。