Effort Tracking SQL Tables for TFS 2012

Team Foundation Server 2012 has some great new features for tracking effort by team member for each iteration / sprint. My article won’t focus on those features as much as it will focus on WHERE the data is stored in SQL. If you want to read more about project management features of TFS 2012, check out this article: http://www.devproconnections.com/article/visual-studio-2011/tfs-2012-143104.

My team uses some cool metrics to show individual burn down vs team burn down.  We keep it simple by reporting this data via Excel or SSRS. In order to make the query dynamic, however, we need to know what the current iteration is and how long it lasts.  The start and end date values are stored in a table in your collection database called tbl_nodes.  The following query grabs all of you iterations that have start and end dates and sorts them in chronological order.  From there, you can link to other records using the GUIDs in the id, parent_id, project_id fields.


select *
from dbo.tbl_nodes
where [start_date] is not null
and [finish_date] is not null
order by start_date, finish_date

So let’s get the current iteration. This query returns all iterations where the current date is between the start and finish date of the iteration.  This returns 4 records with my configuration.


select *
from dbo.tbl_nodes
where [start_date] <= getdate() and [finish_date] >= getdate()
order by start_date, finish_date

However, in my case, I have nested iterations to manage a rather LARGE project.  We have 3 week iterations in managing / maintaining a code base that has evolved over a few years.  For example:

  • Level 0: \ProjectName\Iteration\
  • Level 1: \ProjectName\Iteration\2013
  • Level 2: \ProjectName\Iteration\2013\Qtr 1
  • Level 3: \ProjectName\Iteration\2013\Qtr 1\20130104-20130124

So if this structure holds true consistently, the following query will return a single record:

select top 1 *
from dbo.tbl_nodes
where [start_date] <= getdate() and [finish_date] >= getdate()
order by [level] desc

About these ads

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s