Overview

The effectiveness of a release or iteration can only be evaluated by the number of test cases. The number of requirements that are covered by test cases excluding summary level requirement use cases can be used as an effectiveness of quality effectiveness. 

Test Case Coverage SQL Query

Given below is the query.

select 
  RL.name as name, 
  COUNT(RT.test_case_id) as TCOUNT 
from 
  SpiraTestEntities.R_RequirementTestCases as RT
join 
  SpiraTestEntities.R_Requirements as R on 
  R.requirement_id = RT.requirement_id and 
  R.project_id = RT.project_id and 
  R.is_deleted = False and 
  R.is_summary = False
join 
  SpiraTestEntities.R_TestCases as T on 
  T.test_case_id = RT.test_case_id and 
  T.project_id = RT.project_id and 
  T.is_deleted = False
join 
  SpiraTestEntities.R_Releases as RL on 
  R.project_id = RL.project_id and 
  R.release_id = RL.release_id  and 
  RL.is_deleted = False and 
  RL.is_summary = False
where 
  RT.project_id = ${ProjectId} 
group by 
  RL.name

Query Explanation

  • Since the focus is on requirements connected with releases and test cases, the three required tables are joined.
  • The query computes the test cases associated with the release.
  • The summary and deleted requirements are suppressed in the results by using the is_summary = False and is_deleted = False.
  • The deleted test cases are removed by is_deleted = False

Output

Given below is the output.

Quality - Test Case Coverage