Overview

Let us recap some definition. 

  • Lead Time is  identified as the time it took for any requirement to be completed from the time it was identified. So, lead time is the difference between CREATION_DATE and END_TIME.
  • Cycle Time is identified as the time it took for any requirement to be completed from the time actual work on that item was started. So, cycle time is the difference between START_DATE and END_DATE.
  • The following diagram illustrates when lead time and cycle time applies and what date fields in the task module drive these computations.

Lead and Cycle Time Overview

Lead Time Diagnostic SQL Query

In order to compute the lead time, the END_DATE can't be older than the CREATION_DATE. Spira automatically creates the CREATION_DATE but the START_DATE and END_DATE are manually entered depending on the workflow when these requirements are adequately refined and prioritized to enter the flow.  So, if users had input a date that invalidates the date constraint required for computing the lead time, then, these must be appropriately updated.  

Run the following query as a report and use the TASK_ID to update the incorrect data entries.

select 
  R.TASK_ID, R.NAME, 
  R.CREATION_DATE, 
  R.START_DATE, 
  R.END_DATE 
from 
  SpiraTestEntities.R_Tasks as R 
where 
  R.PROJECT_ID = ${ProjectId} and 
  R.Is_Deleted = False and
  R.TASK_STATUS_NAME = "Completed"
  DiffDays(R.CREATION_DATE, R.END_DATE) < 0

Query Explanation

  • The ${ProjectId} is used to bring up tasks for the currently selected project. If you eliminate this clause, it will bring cross-product tasks. To make your query set workable, then, you may have to bring project_id and join with the Projects table for project related information like the project name.
  • The Is_Deleted is used to eliminate the deleted tasks for consideration.Since the focus is only on completed status, the task_status_name is checked for tasks in that status.
  • If end date was older than the creation date, then, difference between creation date and end date will be negative. So, DiffDays function is used to compute the difference and compare for negative numbers (<0).

Query Output

The following illustration shows how you can use the output of the diagnostic SQL query to correct the issues you may have in your data.

Next Steps

To correct the data, do the following:

  1. Take the Task_ID in the report (e.g.: )
  2. Go to the task in the artifact in the specific project
  3. Go to the specific task in the detail view. 
  4. Update the END DATE on when the task actually got completed. 
  5. If the START DATE does not reflect the right date when the task was started, then, update that as well.  

Once the data looks good, you are ready to aggregate the data for lead time and cycle time and graph these lagging metrics.

Notable Exceptions

Spira enforces that the end date is always greater than the start date. However,  Spira does not enforce the start date is greater than creation date. This choice is deliberate because we allow integration with other providers to import the data and work may have already started on some of these artifacts in the other tools. In such cases, when artifacts are imported into Spira, the creation date in Spira will be the date the artifact was created in Spira.  Additionally, it is possible for someone to log a task or incident after work has been started for unknown materialized risks (issues)  for auditability and traceability. If your data is subject to such exceptions, then, the lead time will only be negative and can not be computed for such artifacts. 

Graphing Lead Time and Cycle Time

When the data is looking good, you can graph the lead time and cycle time.