Query sprint hours by team member (estimated vs completed)

Returns aggregated original estimate, remaining work, and completed work per team member for a sprint (iteration path). Useful for sprint burndown or capacity reporting. The query groups by iteration path and assignee; the WITH clause filters to a specific sprint and state (e.g. Resolved, Closed).

This only gives meaningful results if your team updates Original Estimate and Completed Work on work items as they progress. You can change the iteration path in the query to cover other sprints or the whole product.

Standard SQL query example

This is the base query accepted by the connector. To execute it in SQL Server, you have to pass it to the Data Gateway via a Linked Server. See how to accomplish this using the examples below.

SELECT 
	  IterationPath as Sprint
	, AssignedToUniqueName as AssignedTo
	, SUM(OriginalEstimate) as OriginalHours_Total
	, SUM(RemainingWork) as RemainingWork_Total
	, SUM(CompletedWork) as CompletedHours_Total
FROM WorkItems
GROUP BY IterationPath,AssignedToUniqueName
WITH(
	  Project='ProductTesting'
	  
	  --//On Preview UI Change [Max Rows] to use more sample rows - else it will use only 100 rows
	  ,Query='SELECT * FROM WorkItems WHERE [System.IterationPath] UNDER ''ProductTesting\Sprint 1'' and State IN(''Resolved'', ''Closed'')'
	  
	  --Use below to query all sprints for this Product
	  --,Query='SELECT * FROM WorkItems WHERE [System.IterationPath] UNDER ''ProductTesting\'' and State IN(''Resolved'', ''Closed'')'
)

Using OPENQUERY in SQL Server

SELECT * FROM OPENQUERY([LS_TO_AZURE_DEVOPS_IN_GATEWAY], 'SELECT 
	  IterationPath as Sprint
	, AssignedToUniqueName as AssignedTo
	, SUM(OriginalEstimate) as OriginalHours_Total
	, SUM(RemainingWork) as RemainingWork_Total
	, SUM(CompletedWork) as CompletedHours_Total
FROM WorkItems
GROUP BY IterationPath,AssignedToUniqueName
WITH(
	  Project=''ProductTesting''
	  
	  --//On Preview UI Change [Max Rows] to use more sample rows - else it will use only 100 rows
	  ,Query=''SELECT * FROM WorkItems WHERE [System.IterationPath] UNDER ''''ProductTesting\Sprint 1'''' and State IN(''''Resolved'''', ''''Closed'''')''
	  
	  --Use below to query all sprints for this Product
	  --,Query=''SELECT * FROM WorkItems WHERE [System.IterationPath] UNDER ''''ProductTesting\'''' and State IN(''''Resolved'''', ''''Closed'''')''
)')

Using EXEC in SQL Server (handling larger SQL text)

The major drawback of OPENQUERY is its inability to incorporate variables within SQL statements. This often leads to the use of cumbersome dynamic SQL (with numerous ticks and escape characters).

Fortunately, starting with SQL 2005 and onwards, you can utilize the EXEC (your_sql) AT [LS_TO_AZURE_DEVOPS_IN_GATEWAY] syntax.

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT 
	  IterationPath as Sprint
	, AssignedToUniqueName as AssignedTo
	, SUM(OriginalEstimate) as OriginalHours_Total
	, SUM(RemainingWork) as RemainingWork_Total
	, SUM(CompletedWork) as CompletedHours_Total
FROM WorkItems
GROUP BY IterationPath,AssignedToUniqueName
WITH(
	  Project=''ProductTesting''
	  
	  --//On Preview UI Change [Max Rows] to use more sample rows - else it will use only 100 rows
	  ,Query=''SELECT * FROM WorkItems WHERE [System.IterationPath] UNDER ''''ProductTesting\Sprint 1'''' and State IN(''''Resolved'''', ''''Closed'''')''
	  
	  --Use below to query all sprints for this Product
	  --,Query=''SELECT * FROM WorkItems WHERE [System.IterationPath] UNDER ''''ProductTesting\'''' and State IN(''''Resolved'''', ''''Closed'''')''
)'
EXEC (@MyQuery) AT [LS_TO_AZURE_DEVOPS_IN_GATEWAY]