Posts

Showing posts from October, 2015

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

Microsoft Visio and Azure

If you find yourself having to create diagrams for Cloud architectures in Visio, there are nice stencils from Microsoft with all the Azure services !

Here is the download link