Created On:  13 September 2010

Problem:

How do you retrieve a list of test definitions for a particular platform that has at least one associated issue?

Resolution:

You can retrieve a list of test definitions for a particular platform that have at least one issue by querying the following two views:
  • RTM_V_TestDefects: This view returns all test definitions that are linked with at least one issue
  • RTM_V_TestDefinitions: This view returns all test definitions with type, test container, and project information, including attribute fields such as platform (Eg - Windows XP).
See query below:

SELECT .[TestContainerID]
      ,[de].[IssueID_pk]
      ,[de].[Name]
      ,.[TestContainerName]
      ,.[ProjectID]
      ,.[ProjectName]
      ,.[TestID]
      ,.[TestName]
      ,.[Platform]
  FROM [test].[dbo].[RTM_V_TestDefinitions] td RIGHT OUTER JOIN [test].[dbo].[RTM_V_TestDefects] de
      ON .[TestID] = [de].[TestID]
      ORDER BY .[TestID]

The above query will return information for all projects.  This can be further minimised by specifing the particular project(s) you wish to query.  More information on the datbase views can be retireved from Test Manager Help | Database Model | Database Views | Issues Views and Test Planning Views.