My goal is to find out, which items where originally planned for a sprint, which items have been added late and which items where moved out of the sprint late
I'm trying to use the Azure Devops Rest API to query all top level items of an iteration at a specific point in time using the following endpoint:
Azure DevOps Rest API Documentation - Query By Wiql
My first query returns the top level items but sadly only if there are any children. At least I can use asOf since the mode(MustContain) allows it:
SELECT [System.Id], [System.WorkItemType], [System.Title], [System.AssignedTo], [System.State], [System.Tags]
FROM WorkItemLinks
WHERE (Source.[System.IterationPath] = 'Name of the iteration')
AND ([System.Links.LinkType] = 'System.LinkTypes.Hierarchy-Forward')
ORDER BY [Microsoft.VSTS.Common.StackRank] asc, [System.CreatedDate] desc
mode(MustContain)
asof '05/05/2021'
The second query I tried returns all top level iteration items, but I cannot specify asOf, since the mode(Recursive) forbids it:
SELECT [System.Id], [System.WorkItemType], [System.Title], [System.AssignedTo], [System.State]
FROM WorkItemLinks
WHERE [Source].[System.IterationPath] = 'Name of the iteration'
AND [System.Links.LinkType] = 'System.LinkTypes.Hierarchy-Forward'
ORDER BY [System.Id]
mode(Recursive)
How can I query all top level iteration items as they are seen in the Sprint Backlog, at a specific point in time?
By using IN GROUP when filtering for [System.WorkItemType] it is no longer necessary to query for WorkItemLinks which resolves both issues as we are then able to use asOf:
SELECT [System.Id], [System.WorkItemType], [System.Title], [System.AssignedTo], [System.State], [System.Tags]
FROM WorkItems
WHERE [System.IterationPath] = 'Name of the iteration'
AND
(
[System.WorkItemType] IN GROUP 'Microsoft.RequirementCategory' OR
[System.WorkItemType] IN GROUP 'Microsoft.BugCategory'
)
ORDER BY [Microsoft.VSTS.Common.StackRank] asc, [System.CreatedDate] desc
ASOF '05/05/2021'