wondering how can i get the time the WI are on an especific state for specific persons on TFS.

Question

Let's say I have a WI and the work Item had 5 states. Pending, In Dev, On Hold, In QA, Completed. For each one of the states I am getting a time stamp when the state is changed and who is assigned to. now, what  I need is to determine how long is the WI on an specific state. is this possible? thanks.

Answer 1

Jorge A Cuervo 78,

You can pull this from the database or through the TFS API.

To pull from the API, you can reference the WorkItem.History Property (see here: http://msdn.microsoft.com/en-us/library/microsoft.teamfoundation.workitemtracking.client.workitem.history.aspx).  Here is an example of someone who blogged about it for TFS 2008: http://adallow.wordpress.com/2008/11/10/tfs-getting-workitem-history-from-the-api/.

To pull it from the Database, you will need to look in two different tables, depending on the current state and previous values of the work item.  Within the project collection database, you will find two tables: dbo.WorkItemsLatest and dbo.WorkItemsWere.  The dbo.WorkItemsLatest has the current value (or last modified value) of the work item.  The dbo.WorkItemsWere will have all of the history versions for that associated work item.  Within these tables, you have the Changed Date value and Revised Date value.  This would allow you to determine time it resided if you calculate the delta between the two fields.

Both tables have all the various pieces of data.  The ID field is your work item #.  Note that there are "funny" named columns, such as FLD10074 which are various data points for that work item.  To figure out what those columns are, you can look in the dbo.Fields table.  The FLDID column is equated the "funny" column name in the dbo.WorkItemsWere and dbo.WorkItemsAre table.  Therefore, you could do a simple query:  Select Name from dbo.Fields where FLDID = '10074'

This would return you back the name of the associated field.

Thanks,
Mike Haun