WIQL: Get only top level items in Iteration asOf specific date

Question

What & Why

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

---

How

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)

---

Question

How can I query all top level iteration items as they are seen in the Sprint Backlog, at a specific point in time?

Answer 1

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'