0

I am trying to get all current project names and request names from TFS that are not closed and have hotfix as '1'. This is the query I used

Select Distinct  wi.System_Title,TP.ProjectNodeName
FROM Tfs_Warehouse.dbo.FactCurrentWorkItem FCWI

INNER join Tfs_Warehouse.dbo.DimTeamProject TP
ON  FCWI.LastUpdatedDateTime = TP.LastUpdatedDateTime
INNER JOIN Tfs_Warehouse.dbo.DimWorkItem wi
        ON TP.ProjectNodeSK = wi.TeamProjectSK
WHERE
wi.CDS_Project_HotFixRelated LIKE '1' AND
System_state NOT LIKE 'Closed'
AND wi.System_IsDeleted  = '0'
AND wi.System_WorkItemType  LIKE 'Request'
Order by System_Title DESC

If I use Tfs_Warehouse.dbo.DimWorkItem instead of FactCurrentWorkItem I can get results but the problem with Tfs_Warehouse.dbo.DimWorkItem is that it shows all the history too, including states of the project that does not exist anymore

Glenville Pecor
  • 97
  • 10
  • You can check the [FactCurrentWorkItem table relationship](https://learn.microsoft.com/en-us/azure/devops/report/sql-reports/table-reference-current-work-items?view=azure-devops-2020), Hope it is helpful. – Levi Lu-MSFT Sep 29 '20 at 09:01
  • I realized that I am using TFS 2018 and that it does not use `FactCurrentWorkItem` like DevOps2020 does – Glenville Pecor Sep 29 '20 at 21:09
  • @LeviLu-MSFT Unless I am mistaken. I thought FactCurrentWorkItem would show all live items, not items that do not exist anymore (e.g. name changes on a request) – Glenville Pecor Oct 01 '20 at 19:32

0 Answers0