Query to count test and failures

Question ID: 108746
0
0

Hello everyone I have encountered the code below which looks good but it appears to be double counting for all runs which I do not desire. I need a query that will count the number of requirements and last test run associated with the test run whether it passed, failed, incomplete, and if it failed i’d like to see the number of defects associated against it as either "closed" or "not closed" for defect status. Currently this query will tally and add up all test runs so if you run two test cases in a test set 10 times each time it will count 20 runs for the linked requirement but I only care about the last run so add only the last two runs. I would like to see something like requirement X run 2 times one pass one fail for the fail 5 defects total, 4 closed and one not closed (assuming the last test run had 5 defects against it). This is the query I have:

SELECT DISTINCT(REQ.RQ_REQ_ID) AS "Requirement ID",
REQ.RQ_REQ_NAME AS "Requirement",
COUNT(TEST.TS_NAME) AS "Total No. of Tests",
SUM(CASE when TEST.TS_EXEC_STATUS like’%Failed%’ then 1 else 0 end) AS "No. Of Failed Tests",
SUM(CASE when TEST.TS_EXEC_STATUS like ‘%Passed%’ then 1 else 0 end) AS "No. Of Passed Tests",
SUM(CASE when TEST.TS_EXEC_STATUS like ‘%No Run%’ then 1 else 0 end) AS "No. Of No Run Tests",
SUM(CASE when TEST.TS_EXEC_STATUS like ‘%Not Completed%’ then 1 else 0 end) AS "No. Of Not Completed Tests",
SUM(CASE when TEST.TS_EXEC_STATUS like ‘%N/A%’ then 1 else 0 end) AS "No. Of Not Applicable Tests"
FROM REQ, REQ_COVER, TEST
WHERE REQ.RQ_REQ_ID = REQ_COVER.RC_REQ_ID
AND TEST.TS_TEST_ID = REQ_COVER.RC_ENTITY_ID
GROUP BY REQ.RQ_REQ_ID, REQ.RQ_REQ_NAME

Marked as spam
Posted by (Questions: 18, Answers: 17)
Asked on November 15, 2018 12:16 am
5 views