TFS Reporting Recipes #2: Get Daily Values for a Date Range

22/10/2012

no comments

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

Add comment
facebook linkedin twitter email

Leave a Reply

Your email address will not be published.

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*