Definition Overview

  • Response time is determined by the time elapsed between the time an incident was logged and the time work began on that incident. In Spira, this can be computed by the difference between creation date and start date.
  • Turn around time is determined by the time elapsed between the time work began on that incident and the time the work was completed. In Spira, this can be computed by the difference between start date and closed date. Please note that agile approaches also call this as the cycle time.
  • Lead time, often computed in the agile context, is the time elapsed between the time an incident was logged and the time the work was completed. 
The equation, therefore, is LEAD TIME = RESPONSE TIME + CYCLE TIME (TURN AROUND TIME).

 

SQL Query

select 
 R.INCIDENT_ID, 
 R.NAME, 
 R.CREATION_DATE, 
 R.START_DATE, 
 R.CLOSED_DATE,  
 CASE WHEN R.START_DATE IS NOT NULL
  Then DiffDays(R.CREATION_DATE, R.START_DATE) 
  else 0
 end as RESPONSE_TIME, 
 CASE WHEN R.CLOSED_DATE IS NOT NULL
  Then DiffDays(R.START_DATE, R.CLOSED_DATE)
  else 0
 end as TURN_AROUND_TIME, 
 R.INCIDENT_STATUS_NAME 
from SpiraTestEntities.R_Incidents as R 
where 
 R.PROJECT_ID = ${ProjectId} and 
 R.IS_DELETED = False and 
 R.INCIDENT_STATUS_NAME = "Closed"

Query Explanation

  • DiffDays is an ESQL function used to compute the difference between two dates giving the result in number of days.
  • In order to compute the response time, the start date can not be null. So, the first CASE statement applies that logic to compute the response time.
  • In order to compute the turnaround time (TAT), the closed date can not be null. So, the second CASE statement applies that logic to compute the TAT.
  • The WHERE clauses eliminates records deleted from the system, filters for the current project, and removes details where the incident status is not in a closed state.

Example Scenario

Given below is the data set up for validating the query.

RT and TAT Data Setup

Report Output

Given below is the report output

RT and TAT Output

Additional Thoughts

Depending on the business process flow, the "Closed" may be an intermediate status before the work is deployed to production environment. Spira allows customizable incident status where this information can be used. In such cases, if the TAT is measuring to that intermediate status, then, this query will work. But, if there is another final status (e.g.: RELEASED) and there is another custom date property governing "RELEASED_DATE", then, that date must be used in the query for TURN_AROUND_TIME and the final status in the where clause.