Overview

As test cases are developed over time, it is a good practice to evaluate how these are progressively getting converted into an automated test case. So, looking at the number of automated test cases also evaluates how productivity can be improved and quality of regression can be augmented. 

Automated Test Cases SQL Query

Given below is the query.

select 
  p.name, 
  p.type, 
  count(p.tcount) as mycount from 
  (select 
     T.NAME, 
     'Automated Test Cases' as type, 
     COUNT (T.TEST_CASE_ID) as tcount from SpiraTestEntities.R_TestRuns as TR
    join SpiraTestEntities.R_TestCases as T on 
      T.Test_Case_ID = TR.TEST_CASE_ID and 
      T.PROJECT_ID = TR.PROJECT_ID and 
      T.IS_DELETED = False
    where TR.PROJECT_ID = ${ProjectId} and tr.test_run_type_id= 2
    group by T.NAME) as p
group by 
  p.name, 
  p.type

Query Explanation

  • This query is made of an inner and outer queries.
  • The inner query counts the number automated test cases (TEST_RUN_TYPE_ID = 2 is Automated).
  • The outer query accumulates the number of automations ignoring the test case for easier reporting.
  • To avoid deleted test cases confusing the results, apply the IS_DELETED = False.
  • If you want this query to also filter by the current release selected in interface, bring the RELEASE_ID = ${ReleaseId}.

Output

Given below is the output.

Quality - Automated Test Cases