Problem:

How can I write an SQL query that will return the Requirements Status Overview from all active projects in SilkCentral TestManager?

Resolution:

QUESTION
----------------------------------
How can I write an SQL query that will return the Requirements Status Overview from all active projects in SilkCentral TestManager?

ANSWER
----------------------------------
The following is an SQL example of how to retrieve the Requirements Status Overview from all the active projects in SilkCentral TestManager.

SELECT rcs.ReqCoverageName AS "Coverage Status",
              Count(rc.ReqTreeNodeID_pk_fk) AS "No. of Requirements"
              FROM TM_ReqCoverageNames rcs LEFT JOIN
              (SELECT r.ReqTreeNodeID_pk_fk, req.Priority,
              MAX(CASE when td.CoverageStatus is null then 4
              else td.CoverageStatus end) AS CoverageStatus
              FROM TM_RequirementsCoverages r
              INNER JOIN TM_RequirementTreeNodes req
              ON (req.ReqTreeNodeID_pk = r.ReqTreeNodeID_pk_fk)
              LEFT JOIN
              (SELECT tds.TestPlanNodeID_pk_fk AS TestID,
              CASE when tds.CurrentExecStatus > 3 then 3
              else tds.CurrentExecStatus end AS CoverageStatus
              FROM TM_TestDefinitions tds) td ON (r.TestPlanNodeID_pk_fk = td.TestID)
              WHERE req.MarkedForDeletion = 0               
              GROUP BY r.ReqTreeNodeID_pk_fk, req.Priority) rc
              ON (rc.CoverageStatus = rcs.ReqCoverageID_pk)
              GROUP BY rcs.ReqCoverageID_pk, rcs.ReqCoverageName
              ORDER BY rcs.ReqCoverageID_pk

The original can be found in SilkCentral by going to

1. TestManager > Reports
2. Select any project
3. Select Requirements Coverage
4. Select Requirements Status Overview
5. Select the Properties tab
6. Select Edit

If you scroll down the SQL statement you will find that the only difference between that statement and this is that the PROJECT_ID statement has been removed.