Created On:  14-05-2010

Problem:

How can I modify the existing "Test Definition Status Overview" report to combine the "Not Executed" and "Not Scheduled" values, into one "Not Executed" value?

Resolution:

The follow SQL queries uses a modified CASE statement to force a "Not Scheduled" value to a "Not Executed" value. As there are minor syntax differences between SQL Server and Oracle, the following article provides an example for database servers.

SQL Server:
SELECT sn.StatusName AS Status,
              COUNT(td.TestID) AS "No. of Test Definitions"
              FROM (SELECT * FROM TM_TestDefStatusNames tdsn
              WHERE tdsn.StatusID_pk=1 OR tdsn.StatusID_pk=2 OR tdsn.StatusID_pk=3) sn LEFT JOIN
              (
              SELECT tds.TestID,StatusId=CASE tds.CurrentExecStatus
                            when 8 then 1
                            when 9 then 2
                            when 4 then 3
                            else tds.CurrentExecStatus
                            end
              FROM RTM_V_TestDefinitions tds
              WHERE ProjectID = ${$PROJECTID}
              ) td
              ON (td.StatusId = sn.StatusID_pk)
              GROUP BY sn.StatusID_pk, sn.StatusName
              ORDER BY sn.StatusID_pk


ORACLE:
SELECT sn.StatusName AS Status,
              COUNT(td.TestID) AS "No. of Test Definitions"
              FROM (SELECT * FROM TM_TestDefStatusNames tdsn
              WHERE tdsn.StatusID_pk=1 OR tdsn.StatusID_pk=2 OR tdsn.StatusID_pk=3) sn LEFT JOIN
              (
              SELECT tds.TestID,(CASE tds.CurrentExecStatus
                            when 8 then 1
                            when 9 then 2
                            when 4 then 3
                            else tds.CurrentExecStatus
                            end) StatusId
              FROM RTM_V_TestDefinitions tds
              WHERE ProjectID = ${$PROJECTID}
              ) td
              ON (td.StatusId = sn.StatusID_pk)
              GROUP BY sn.StatusID_pk, sn.StatusName
              ORDER BY sn.StatusID_pk

Incident #2450499