CODEWITHSUNDEEP
tfs
cristiano1974
cristiano1974

Reputation: 21

task and subtask in TFS

I'd like to know which is parent/child relationship for task in th TFS datawarehouse or db.

For example the parentID=15662 and childID=15667, where I can find this information in the DW or db?

Upvotes: 2

Views: 1484

Answers (1)

Paul G
Paul G

Reputation: 2742

I think this will get you close to what you need. It just pulls the parent UserStory with a particular ID and all of it's children. The work item types may need to be changed based on your team project template. You can look at the different link types in DimWorkItemLinkType and adjust the query accordingly to get the desired result. 

USE Tfs_Warehouse
GO

SELECT      story.System_ID AS StoryID
            , story.System_Title AS StoryTitle
            , task.System_ID AS TaskID
            , task.System_Title AS TaskTitle
            , linkType.ReferenceName
            , linkType.LinkName
FROM        CurrentWorkItemView story   
LEFT JOIN   dbo.vFactLinkedCurrentWorkItem linkToStory
ON          story.WorkItemSK = linkToStory.SourceWorkItemSK
LEFT JOIN   CurrentWorkItemView task
ON          linkToStory.TargetWorkitemSK = task.WorkItemSK
AND         task.System_WorkItemType = 'Task'
LEFT JOIN   dbo.DimWorkItemLinkType linkType
ON          linkToStory.WorkItemLinkTypeSK = linkType.WorkItemLinkTypeSK
WHERE       story.System_WorkItemType = 'User Story'
AND         story.System_ID =  15662

Upvotes: 1

Related Questions