Break it, fix it

You might find yourself having to track down bad practices (like modifying the original estimate of a task). You could setup an alert to be notified as soon as it happens or you can monitor changes through the TFS database. Today, I'll explain how to monitor using a query on the TFS database. We will use the collection database. The idea is to use the views related to work items and their history. The first step is to union the data sets (the current work item states and the past work item states). Then we select the record having an original estimate higher than the previous version. THAT is the tricky part. I used the LAG analytic function to get the reference to the "previous" record (that is, the same id but the previous revision). Here is the complete statement. WITH AllData AS (   SELECT *     FROM [Tfs_DefaultCollection].[dbo].[WorkItemsAreUsed]    UNION *     FROM [Tfs_DefaultCollection].[dbo].[WorkItemsWereUsed] ), AllDat...

If you ever need to retrieve the parameters (and respective values) of a test case (for reporting purposes for example), the database table to read is Tfs_<CollectionName>.dbo.WorkItemLongTexts . It contains the HTML/XML field values for each workitem. The only missing part is the ID of the actual field you want to retrieve : FldId 10018 contains the definition of the parameters FldId 10029 contains the values of the parameters