Days between dates in Azure DevOps work items by query

0
votes

What I would like to do

I am an Azure DevOps Services user.
Are there any ways to calculate the duration between dates in work items by Azure Boards query?
Or, are there any fields of days between dates?

For example, I would like to display a list of work items which duration between Activated Date and Created Date exceeds 5 days.

What I tried

I tried Activated Date > [Field] Created Date + 5 but result is
TF51005: The query references a field that does not exist. The error is caused by Created Date + 5.

I wonder if only supported macros like @Today or @StartOfDay are able to be used for calculation in query editor.
I read the following official references but I could not reach a solution.

If there are no solutions by query, another ideas are welcome.

1
azure-devopsazure-boards

1 Answers

1
votes

This cannot be done by query. You will need to use rest api as workaround. But it is complicated and might not achieve the same effect as Query.

1, First you can use work item wiql api to query those items that [Microsoft.VSTS.Common.ActivatedDate] > [System.CreatedDate] to get the ids of workitems

2, Then you can use work item list api to list the fields details of those work items queried by above step.

3, Last use powershell scripts to filter those work items whose duration between Activated Date and Created Date exceeds 5 days

Please check below example in powershell scripts:

For {PAT} please check here to get a Personal Access Token to Authenticate below API calling

# query those items that [Microsoft.VSTS.Common.ActivatedDate] > [System.CreatedDate] and get the ids of workitems

$qurl =  "https://dev.azure.com/{org}/{proj}/_apis/wit/wiql?api-version=5.1"

$WIQL_query = "Select [System.Id], [System.Title], [System.State],[Microsoft.VSTS.Common.ActivatedDate],[System.CreatedDate] From WorkItems Where [Microsoft.VSTS.Common.ActivatedDate] > [System.CreatedDate]"
$body = @{ query = $WIQL_query }
$bodyJson=@($body) | ConvertTo-Json

$pat = {PAT}

$base64AuthInfo= [System.Convert]::ToBase64String([System.Text.Encoding]::ASCII.GetBytes(":$($pat)"))

$result = Invoke-RestMethod -Uri $qurl -Headers @{authorization = "Basic $base64AuthInfo"} -Method post -ContentType "application/json" -Body $bodyJson

# GET THE WORKITEM IDS

$ids = $result.workItems | select id | foreach{ $_.id }
$id= '{0}' -f ($ids -join ",")

# use work item list api to list the fields details of those work items 

$url = "https://dev.azure.com/{ORG}/{PROJ}/_apis/wit/workitems?ids=$($id)&api-version=5.1"

$result1 = Invoke-RestMethod -Uri $url -Headers @{authorization = "Basic $base64AuthInfo"} -Method get

# Filter those workitems whose duration between Activated Date and Created Date exceeds 5 days.

$result1.value.fields | where {[datetime]$_.'Microsoft.VSTS.Common.ActivatedDate' -gt ([datetime]$_.'System.CreatedDate').AddDays(5)}

Hope above helps!