Purpose
One of the most commonly required features in a report is a list of the daily values of some metric over a range of dates (for example, how many open bugs were in the system at the end of the day for each day in the last month). This query will retrieve these metric values for each day in the date range.
Prerequisites
- TFS 2010 or higher
- @StartDate parameter – The beginning of the required date range
- @EndDate parameter – The end of the required date range
Query
SELECT dd.[Date], query.Value
FROM dbo.DimDate dd
CROSS APPLY
(
SELECT 1 AS Value — Replace with your own query, use dd.[Date] in a WHERE clause to limit the contents based on the date
) query
WHERE dd.[Date] Between @StartDate AND @EndDate
Example
The following example retrieves the number of open bugs for a given date range:
SELECT dd.[Date], query.Value
FROM dbo.DimDate dd
CROSS APPLY
(
SELECT SUM(RecordCount) AS Value
FROM dbo.WorkItemHistoryView
WHERE System_WorkItemType = ‘Bug’
AND ProjectNodeGUID = @ProjectGUID
AND System_State = ‘Open’
AND System_ChangedDate <= dd.[Date]
AND System_RevisedDate > dd.[Date]
) query
WHERE dd.[Date] Between @StartDate AND @EndDate