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.

 

Cycle Time Per Sprint

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:  )