Created On:  15 November 2012

Problem:

There is no prewritten SCTM report that will return the details of the first run of a test.  See information and SQL below that can be used to extract this information from the SCTM database.  This can then be used in a custom SCTM report.

Resolution:

For this report you will be required to select the first row in an SQL group (to get the first run of a test).  This is further explained in the following atricle:
http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/

Please see sample SQL below that queries TM_V_TestDefExecutions view using a correlated subquery.  This view returns the execution runs for all Test Definitions, including status.  I selected the minimum Execution Run for each Test (which should be the first run).  This can be verified by the field 'ExecutionTimestamp'.  See SQL below:-

SELECT ExecDefRunID_fk, TestDefID_fk, TestDefName, StatusName, TestDefPath, ExecutionTimestamp
FROM TM_V_TestDefExecutions t1
WHERE ExecDefRunID_fk =
(Select MIN(ExecDefRunID_fk) from TM_V_TestDefExecutions t2
WHERE t2.TestDefID_fk = t1.TestDefID_fk)
ORDER BY TestDefID_fk
Incident #2585979