Overview

One of the critical considerations for software stability and deployment readiness is the time taken (cycle time) for an incident or defect resolution. This decision is often a criteria in the go/no-go decision for deployment consideration. Such decisions are based on the priority of the incident or severity of the incident depending on the organization. In this query, the priority of the defect is used for aggregating the cycle time for effective defect triaging process. 

Defect Resolution Cycle Time SQL Query

Given below is the query.

select 
  case when R.PRIORITY_NAME is null then "Unassigned" else R.PRIORITY_NAME end as PRIORITY, 
  sum((DiffDays(R.CREATION_DATE, R.CLOSED_DATE) /7)) as LEAD_TIME_WEEKS 
from 
  SpiraTestEntities.R_Incidents as R 
where 
 R.PROJECT_ID = ${ProjectId} and 
 R.IS_DELETED = False and 
 R.INCIDENT_STATUS_NAME = "Closed" 
group by 
  R.PRIORITY_NAME
order by 
  R.PRIORITY_NAME

Query Explanation

  • This query uses a case when else logic to assign an "Unassigned" priority when the PRIORITY_ID is null.
  • The time difference between the CREATION DATE and CLOSED DATE is computed as the cycle time. In this example query, the days are divided by 7 to determine the number of weeks. If the duration is expected in months, then, you can divide by 30. 
  • If large outliers exists, you can also use lesser than operator in the WHERE clause to remove those outliers.
  • Since this is looking at completed incidents, the INCIDENT_STATUS_NAME = "Closed" is applied. If all incident statuses are required, additional case when else logic must be applied in cycle time computation where the incident status is not closed to use the current date as the closed date.
  • The IS_DELETED = False removes any incidents deleted from the interface.

Output

Given below is the output.

Quality - Defect Resolution Time