Query by date or current iteration in Azure Boards

TFS 2017 | TFS 2015 | TFS 2013

The @Today and @CurrentIteration macros are useful for listing work items based on relative dates or their assignment to a team's current iteration. To list work items based on when they were created, closed, resolved, or changed state—use @Today or specify dates. For queries that list work items based on their assignment to a team's current sprint, use @CurrentIteration.

For example, you can find work items that were modified in the last three days with the following query.

Supported operators and macros

Query clauses that specify a DateTime field or the Iteration Path can use the operators and macros listed in the following table.

Notes:

1. The @StartOfDay, @StartOfWeek, @StartOfMonth, @StartOfYear macros are supported for Azure DevOps Server 2019.1 and later versions, and only when run from the web portal.
2. The @CurrentIteration macro is supported for TFS 2015 and later versions, and only when run from the web portal.
3. The @CurrentIteration +/- n macro is supported for Azure DevOps Server 2019 and later versions, and only when run from the web portal.

Client restrictions on the use of the @CurrentIteration macros

You can use the @CurrentIteration in a query from the following clients:

• Web portal that connects to Azure Boards
• Web portal that connects to an on-premises Azure DevOps 2015 or later version
• Visual Studio 2015 or Team Explorer 2015 or later versions connected to Azure Boards or TFS 2015 or later versions.
• Using the REST API

You can use the @CurrentIteration +/- n macro in a query against Azure Boards, Azure DevOps Server 2019, and later versions, and with a REST API that includes the team as a parameter, for example, @CurrentIteration('[Project]/Team').

An error occurs if you open a query that contains the @CurrentIteration macro in earlier versions of Visual Studio, or from Excel or Project. Also, you can't use the macro when copying or cloning test suites and test cases, defining alerts, or with REST APIs.

Date-based queries

You can filter for work items by the date on which they were changed or for a specific time period. Limiting the scope of your query can help with performance by only returning results that fit the date range that you include. If you're new to creating queries, see Use the query editor to list and manage queries.

Not all fields are valid for all work item types (WITs). Jump to date fields for the set of fields you can include in queries and which WITs they apply to.

Items created in the last 30 days

Items modified on a specific date

Items resolved today

Items closed within a specified time period

Items that haven't been closed (Closed Date is null)

Items whose status was updated within the last week

Items closed during the current sprint (the @CurrentIteration macro references the sprint defined for the current team context)

Create queries for your team's current iteration

If your team follows Scrum processes, you schedule work to be completed in sprints. You can track the progress of requirements, bugs, and other work to be completed in the current sprint using the @CurrentIteration macro.

Any item assigned to a sprint that corresponds to the current iteration path for the team is found. For example, if a team is on Sprint 5, the query returns items assigned to Sprint 5. Later, when the team is working in Sprint 6, the same query returns items assigned to Sprint 6.

Before creating or updating a query to use the @CurrentIteration macro, make sure you select your team. The @CurrentIteration macro references the current team selected in the web portal.

List work items moved out of a sprint

You can list work items that were defined for a sprint but later moved out using a query with a clause that contains the Was Ever operator for the Iteration Path. You can only construct this query using the WIQL syntax. You can edit the WIQL syntax in the Query Editor by installing the Wiql Editor Marketplace extension.

For example, the following syntax queries for work items that meet the following criteria:

1. Defined in the current project
2. Work item type equals User Story or Bug
3. Work items are under the Fabrikam Fiber Web team Area Path
4. Work items aren't in a Closed, Completed, Cut, or Resolved state
5. Not in the current iteration path for the Fabrikam Fiber Web team
6. But were assigned to the current iteration path for the Fabrikam Fiber Web team
7. Are now assigned to the Current iteration +1 for the Fabrikam Fiber Web team
8. And were changed within the last 30 days (the length of the sprint)

SELECT
    [System.Id],
    [System.WorkItemType],
    [System.AssignedTo],
    [System.Title],
    [System.State],
    [System.Tags],
    [System.IterationPath],
    [System.AreaPath]
FROM workitems
WHERE
    [System.TeamProject] = @project
    AND [System.WorkItemType] IN ('User Story', 'Bug')
    AND [System.AreaPath] UNDER 'FabrikamFiber\Web'
    AND NOT [System.State] IN ('Completed', 'Closed', 'Cut', 'Resolved')
    AND NOT [System.IterationPath] = @currentIteration('[FabrikamFiber]\Web <id:cdf5e823-1179-4503-9fb1-a45e2c1bc6d4>')
    AND (
        EVER (
            [System.IterationPath] = @currentIteration('[FabrikamFiber]\Web <id:cdf5e823-1179-4503-9fb1-a45e2c1bc6d4>')
        )
        AND [System.IterationPath] = @currentIteration('[FabrikamFiber]\Web <id:cdf5e823-1179-4503-9fb1-a45e2c1bc6d4>') + 1
        AND [System.ChangedDate] >= @today - 30
    )
ORDER BY [System.Id]

The Query Editor view of the syntax appears as shown.

List work items added to a sprint after the start date

To list newly created work items added to a sprint after its start date, use a query similar to the one shown in the following image. This query works by filtering for work items assigned to the current sprint but were created after the start of the sprint date. In this example, this is achieved with the clause created Date = @Today - 28.

For other options for querying changes to sprint scope, see About Sprints, Scrum and project management, Sprint scope change.

Date and Iteration Path fields

You can use date fields to filter your queries. Some of these fields are populated with information as a work item progresses from one state to another. Several of these fields don't appear on the work item form, but are tracked for those WITs listed in the following table.

Notes:

1. See also Query by assignment or workflow changes, Date, and Identity fields.

2. For these fields to be defined for a WIT, they must be included in the WORKFLOW section of the WIT definition. For example, this syntax is included within the FIELDS definition when transitioning to a Resolved state:

    <FIELD refname="Microsoft.VSTS.Common.ResolvedDate" />  
       <SERVERDEFAULT from="clock"  />  
    </FIELD >  
   

3. Start Date and Finish Date fields are calculated if you create a project plan in Microsoft Project and then synchronize that plan with tasks that are stored in Azure Boards. These fields might not appear on the work item form, but are calculated for the backlog items and tasks that are linked to backlog items. You can view their read-only values in results from a query or from Microsoft Excel.

   Important

   Microsoft Project Integration and the TFSFieldMapping command are not supported for:

   • Visual Studio 2019 and Azure DevOps Office® Integration 2019
   • Azure DevOps Server 2019 and later versions, including Azure DevOps Services.

   However, full support for Microsoft Excel integration is maintained and supports bulk import and update of work items. Alternatives to using Microsoft Project include the following:

   • Delivery plans
   • A Marketplace extension such as Project Connect or GANTT chart.

Related articles

To query for items based on text entered in the History field, see History and auditing.

• Query by assignment or workflow changes
• Query quick reference
• Define iteration (sprint) paths and configure team iterations
• Create managed queries with the query editor
• Query operators & macros
• Work item field index
• Query permissions

SDK resources

To programmatically interact with queries, see Query for Bugs, Tasks, and Other Work Items.