具有变更集的工作项或具有变更集的链接项的数量 [英] Number of WorkItems with changesets or linked items with changesets

问题描述

我们有一个正在使用的系统，开发人员将使用 TFS 2010 中的新支持票"工作项记录支持票.如果结果发生代码更改，他们会将更改集链接到此工作项类型.或者，如果系统中存在需要更改代码的相关错误，他们会将支持票链接到错误，然后签入链接到错误的变更集.

We have a system in use where developers will log a support ticket using a new 'Support Ticket' work item in TFS 2010. If there's a code change as a result they will link the changeset to this work item type. Alternatively if there's a related Bug in the system that requires a code change they will link the Support ticket to the bug and then check in the changeset linked to the bug.

我正在尝试使用 TFS 或最好使用给我的 SQL 提取报告:

I'm trying to pull out a report either in TFS or preferably using SQL that gives me:

• 一个月内创建的支持工单数量.
• 带有已记录变更集的链接错误的支持票的数量
• 更改集直接链接到支持票证的支持票证数量.

有没有办法在 SQL 中做到这一点

Is there a way to do this in SQL

推荐答案

针对您的数据仓库运行此程序.此 SQL 将列出所有工作项及其关联的变更集.我已经注释掉了 WHERE 子句中的一些项目并将它们保留下来，以便让您了解可以过滤的内容，因为我不确定您在 TFS 中设置了什么流程模板.

Run this against your Data Warehouse. This SQL will list all Work Items with their associated Change Sets. I've commented out a few items in the WHERE clause and left them in to give you an idea on what you can filter on as I'm not sure what process template you have setup in TFS.

在 WHERE 子句中注释掉的 df.FilePath 字段允许您过滤特定的存储库/分支.

The df.FilePath field commented out in the WHERE clause allows you to filter on specific repositories/branches.

SELECT DISTINCT --df.[FileName]
    --,df.FilePath
    dwi.System_title AS 'Title'
    ,dcs.ChangesetID AS 'ChangeSetID'
    ,dwi.System_id AS 'WorkItemID'
    ,dwi.System_WorkItemType
    ,dwi.System_State
    ,dwi.System_CreatedDate
    ,dwi.System_ChangedDate

FROM DimFile df 
JOIN FactCodeChurn fcc ON df.FileSK = fcc.FilenameSK
JOIN FactWorkItemChangeset fwi ON fcc.ChangesetSK = fwi.ChangesetSK
JOIN DimWorkItem dwi ON fwi.WorkItemID = dwi.System_id
AND fwi.TeamProjectCollectionSK = dwi.TeamProjectCollectionSK
AND fwi.RemovedDateTime = CONVERT(DATETIME, N'9999', 126)
JOIN DimChangeset dcs ON dcs.ChangesetSK = fcc.ChangesetSK 

WHERE dwi.System_revisedDate = CONVERT(DATETIME, N'9999', 126)
--AND df.FilePath LIKE '%$repositorylocation%'
--AND dwi.System_WorkItemType IN ('Product Backlog Item', 'Task', 'Bug')

ORDER BY dcs.ChangesetID

此代码是针对 TFS 2013 运行的，但我相信 Schema 在 2010 年是相同的

This code was run against TFS 2013, but I believe the Schema is the same in 2010

这篇关于具有变更集的工作项或具有变更集的链接项的数量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！