Overview

Often, the time taken for a test cycle for test cases can consume the time available for a release or iteration. This may also limit the capacity of the work that can be taken on in a release suggesting what time consuming test cases can be broken down or automated.  

Test Cycle Time SQL Query

Given below is the query.

select 
  (cast (TR.TEST_CASE_ID as string) + '-' + cast (TR.TEST_RUN_ID as string)) as  TESTCASE_TESTRUNS,
  case 
    when TR.END_DATE is not null 
       then DiffSeconds(TR.START_DATE, TR.END_DATE) 
       else DiffSeconds(TR.START_DATE, CurrentDateTime()) 
    end as TIME_IN_SECONDS
from 
   SpiraTestEntities.R_TestRuns as TR 
where  
  TR.PROJECT_ID = ${ProjectId} and 
  TR.IS_DELETED = False and 
  TR.TEST_RUN_TYPE_ID = 1  and 
  TR.TEST_SET_ID is null and 
  TR.RELEASE_ID = ${ReleaseId} and 
  TR.EXECUTION_STATUS_NAME <> "Not Run"
order by 
  TR.TEST_CASE_ID, TR.TEST_RUN_ID

Query Explanation

  • Since cycle time is measured for a test run associated with a test case in a release, the query brings both the test case and test run as a concatenated field. The cast to string is required to perform the concatenation as the test case and test run are numbers. 
  • The time in seconds is computed by the ESQL function DiffSeconds by computing the difference between the start and end date timestamps if the end date is present. If the end date is absent, then the current time is substituted (as the test run is still not completed). A case when else logic is applied to perform this operation.
  • The current project and release selection is used by joining with ${ProjectId} and ${ReleaseId}
  • This data set filters only test cases that are not associated with test sets and so the test_set_id is not null is applied. 
  • To avoid deleted incidents  confusing the results, apply the IS_DELETED = False.

Output

Given below is the output.

Quality - Test Case Cycle Time