Я создал еще один запрос для своей организации, который может быть потенциально полезным. Его вывод:
Epic1
-Feature1
-User Story1
-Task1
-Task2
-Task3
-User Story2
-Task1
-Task2
-Feature2
-User Story1
-Task1
-Task2
-Task3
-User Story2
-Task1
-Task2
Epic2
-Feature1
-User Story1
-Task1
-Task2
-Task3
-User Story2
-Task1
-Task2
-Feature2
-User Story1
-Task1
-Task2
-Task3
-User Story2
-Task1
-Task2
Мы используем следующий запрос для создания иерархических рабочих элементов в TFS 2015:
;WITH cte
AS ( SELECT DimTeamProject.ProjectNodeName ,
dimworkitem.System_WorkItemType ,
DimWorkItem.System_Id ,
FactWorkItemLinkHistory.TargetWorkItemID ,
DimWorkItem.System_Title,
DimWorkItem.System_State,
DimWorkItem.Microsoft_VSTS_Common_ActivatedDate,
DimWorkItem.Microsoft_VSTS_Scheduling_TargetDate,
DimWorkItem.System_CreatedDate,
DimWorkItemLinkType.LinkName,
TeamProjectSK,
dimworkitem.System_rev,
CurrentWorkItemView.Microsoft_VSTS_Scheduling_RemainingWork,
CurrentWorkItemView.Microsoft_VSTS_Scheduling_OriginalEstimate,
CurrentWorkItemView.Microsoft_VSTS_Scheduling_CompletedWork,
CurrentWorkItemView.Microsoft_VSTS_Scheduling_StoryPoints,
CurrentWorkItemView.AZDES_SprintPoints,
CurrentWorkItemView.System_AssignedTo,
Row_Number() over(Partition by dimworkitem.system_id,TeamProjectSK, FactWorkItemLinkHistory.TargetWorkItemID Order by dimworkitem.system_rev DESC ) rownum
FROM DimWorkItem ,
DimTeamProject ,
FactWorkItemLinkHistory,
DimWorkItemLinkType,
CurrentWorkItemView
WHERE DimWorkItem.TeamProjectSK = DimTeamProject.ProjectNodeSK
AND DimWorkItem.System_Id = FactWorkItemLinkHistory.SourceWorkItemID
and DimWorkItemLinkType.WorkItemLinkTypeSK = FactWorkItemLinkHistory.WorkItemLinkTypeSK
AND CurrentWorkItemView.System_Id=DimWorkItem.System_Id
and CurrentWorkItemView.ProjectNodeSK=DimWorkItem.TeamProjectSK
/* -To Test the Query using the project Name of our choice- */
--AND DimTeamProject.ProjectNodeName ='Test Project Name Here'
AND DimWorkItem.System_State in ('ACTIVE','NEW')
/* -System Revisions are created when the entry is modified. Onlt the latest entry will have the below revised date- */
AND dimworkitem.System_RevisedDate = '9999-01-01 00:00:00.000'
AND DimWorkItemLinkType.Linkname IN ( 'Parent',
'child' )
GROUP BY DimTeamProject.ProjectNodeName ,
DimWorkItem.System_Id ,
FactWorkItemLinkHistory.TargetWorkItemID ,
DimWorkItem.System_Title ,
dimworkitem.System_WorkItemType,
DimWorkItem.System_State,
TeamProjectSK,
DimWorkItemLinkType.LINKName,
DimWorkItem.Microsoft_VSTS_Common_ActivatedDate,
DimWorkItem.Microsoft_VSTS_Scheduling_TargetDate,
DimWorkItem.System_CreatedDate,
dimworkitem.system_rev,
CurrentWorkItemView.Microsoft_VSTS_Scheduling_RemainingWork,
CurrentWorkItemView.Microsoft_VSTS_Scheduling_OriginalEstimate,
CurrentWorkItemView.Microsoft_VSTS_Scheduling_CompletedWork,
CurrentWorkItemView.Microsoft_VSTS_Scheduling_StoryPoints,
CurrentWorkItemView.AZDES_SprintPoints,
CurrentWorkItemView.System_AssignedTo
)
SELECT distinct t1.ProjectNodeName ,
t1.TeamProjectSK,
t1.System_Id requirement_Id ,
t1.System_WorkItemType,
t1.System_State,
t1.System_Title requirement_title ,
t2.System_Id Change_request_id ,
t1.LinkName,
t2.System_WorkItemType Change_Request_Type,
t2.TeamProjectSK,
t2.System_State change_request_system_state,
t2.System_Title Change_Request_Title,
t1.Microsoft_VSTS_Scheduling_OriginalEstimate,
t1.Microsoft_VSTS_Scheduling_RemainingWork,
t2.Microsoft_VSTS_Scheduling_CompletedWork,
t1.Microsoft_VSTS_Scheduling_StoryPoints,
t1.AZDES_SprintPoints,
t1.System_AssignedTo,
t1.Microsoft_VSTS_Common_ActivatedDate,
t1.System_CreatedDate,
t1.Microsoft_VSTS_Scheduling_TargetDate,
T1.rownum
FROM cte t1
INNER JOIN cte t2 ON t1.TargetWorkItemID = t2.System_Id
and t1.rownum = 1
and t1.TeamProjectSK=t2.TeamProjectSK
--and t1.System_Id=Test System id here
--and t1.TeamProjectSK=Test Team Project SK here
--and t1.projectnodename='Test Project Name Here'
ORDER BY t1.System_Id;
person
Subhan Mohammed Abdul
schedule
15.05.2017