List of latest test runs per test case

Friday, March 25, 2022
Avatar

Hi folks

I would like the have a table with the latest test runs for each test case. Can someone help me with a ESQL query?

2 Replies
Sunday, March 27, 2022
Avatar
re: Pierre Friday, March 25, 2022

Hi Pierre

If you want all the test runs, with test case and execution date, something like:

select TR.TEST_CASE_ID, TR.NAME as TEST_CASE_NAME, TR.RELEASE_ID, TR.RELEASE_VERSION_NUMBER, TR.EXECUTION_STATUS_NAME, TR.TESTER_NAME, TR.END_DATE
from SpiraTestEntities.R_TestRuns as TR
where TR.PROJECT_ID = ${ProjectId} and TR.EXECUTION_STATUS_ID <> 3
order by TR.END_DATE desc

If you want it grouped by test case, then something like:

select TR.TEST_CASE_ID, TR.NAME as TEST_CASE_NAME, TR.RELEASE_ID, TR.RELEASE_VERSION_NUMBER, TR.EXECUTION_STATUS_NAME, TR.TESTER_NAME, TR.END_DATE
from SpiraTestEntities.R_TestRuns as TR inner join

(select TR.TEST_CASE_ID, max(TR.END_DATE) as LAST_EXECUTION
from SpiraTestEntities.R_TestRuns as TR
where TR.PROJECT_ID = ${ProjectId} and TR.EXECUTION_STATUS_ID <> 3
group by TR.TEST_CASE_ID) as GR on TR.TEST_CASE_ID = GR.TEST_CASE_ID and TR.END_DATE = GR.LAST_EXECUTION

where TR.PROJECT_ID = ${ProjectId} and TR.EXECUTION_STATUS_ID <> 3
order by TR.END_DATE desc

Regards
David

Wednesday, March 30, 2022
Avatar
re: inflectra.david Sunday, March 27, 2022

Hi David

Thank you for your reply. It seems that your second example does exactly what i need.

For my understanding: What is the meaning of "TR.EXECUTION_STATUS_ID <> 3"

Statistics
  • Started: Friday, March 25, 2022
  • Last Reply: Wednesday, March 30, 2022
  • Replies: 2
  • Views: 438