Monday, December 27, 2010

Average Cycle Time of Workitems

Find below a query that shows the average Cycle Time per state, you can store the results in a temporary table and join it to provide averages to help in value stream mapping.

 

image Cycle Time Per Sprint
image

The bulk of the work is done by the query below


   1:  WITH cteStateChange(rowId, tpId, wiId, [State], PrevState, UpdTime) AS(
   2:      SELECT Row_Number() OVER(Order by wi.TeamProjectSK, wi.System_id, wi.System_Rev) 
   3:          , wi.TeamProjectSK
   4:          , wi.System_Id
   5:          , wi.System_State
   6:          , wi.PreviousState 
   7:          , wi.System_ChangedDate
   8:      FROM dbo.DimWorkItem wi
   9:      INNER JOIN dbo.FactWorkItemHistory wih
  10:          ON wih.WorkItemSK = wi.WorkItemSK
  11:          AND wih.TeamProjectSK = wi.TeamProjectSK
  12:      WHERE 1=1
  13:      AND (wih.StateChangeCount = 1)
  14:  )