Find all TFS work items for which the original estimate has been changed

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]
),AllDataWithLag AS
(
  SELECT *
       , [Microsoft.VSTS.Scheduling.OriginalEstimate] -
         LAG([Microsoft.VSTS.Scheduling.OriginalEstimate], 1)
         OVER(PARTITION BY [System.Id] ORDER BY [System.Rev]) AS EstimateDifference
   FROM AllData
)
SELECT *
  FROM AllDataWithLag 
 WHERE [System.TeamProject] = '[Replace with project name]'
   AND EstimateDifference > 0
 ORDER BY [System.Id], [System.Rev]