Share via

TFS 2010 method to get Work Items as of a date

  • Article

Quite a while ago I added a blog post about using compensating records to get Work Item data as of a specific data from the Warehouse.

   TFS Warehouse Work Item Tracking Compensating Records

In TFS 2010 we have added a new field, Revised Date, that allows you to find the work item dimension data quickly.  The way it works is that there are two dates associated with each Work Item dimension entry stored in the Warehouse.  A "Changed Date" and a "Revised Date".  I'll be the first to admit that their names don't really tell you much about what they are used for - hopefully this should clear things up a bit.

   Changed Date (the DimWorkItem.System_ChangedDate field in the Warehouse) stores the date that that revision was created.

   Revised Date (the DimWorkItem.System_RevisedDate field in the Warehouse) stores the date that the next revision of the Work Item was created.  If there is no next version (this version is the latest) then this will store the date equal to CONVERT(DATETIME, '9999', 126) - or '9999-01-01 00:00:00.000'.

So if you want to get the Work Item revision for a specific Work Item ID that was the latest version as of '2010-01-04'

SELECT
  wi.[System_Id]
 ,wi.[System_Rev]
 ,wi.[System_Title]
 ,wi.[System_State]
FROM
 [DimWorkItem] wi
JOIN
 [DimTeamProject] tpc
 ON tpc.ProjectNodeSK = wi.TeamProjectCollectionSK
WHERE
 wi.[System_ChangedDate] <= '2010-01-04'
AND wi.[System_RevisedDate] > '2010-01-04'
 AND wi.[System_Id] = 1234
 AND tpc.ProjectNodeName = 'VSTSDF'

You can see how I "box" the date I'm interested in with the Changed Date and Revised Date fields.  Changed Date and Revised Date are full DateTime data types - so you have down to the ms granularity for these fields.  So you could get the valid value as of, say, 5:00 PM on '2010-01-04' as well if you would like.

Just a couple notes on the query above - you will notice that I have added a join to DimTeamProject here as well.  This is because Work Item ids are no longer unique across a TFS deployment.  They are unique to a Team Project Collection (TPC).  So in order to fully specify a Work Item I need to specify both its TPC and Id.

I hope this to be the first of several posts on the new schema within the Warehouse - if anyone has any specific things they would like to see let me know.