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.
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.