How to retrieve the most recent run by a user of an Execution Definition ID from Last Executions?



How to retrieve the most recent run by a user of an Execution Definition ID from Last Executions?


It is necessary to create an SQL statement against the SilkCentral Test Manager database to determine which Execution Definition ID is the last run in an execution run. The tables and view listed below contain the relevant information to determine this.


The following query will return the Last Execution for an Execution Definition

// Borland Code VOzohili
// Date: 03 August 2009

// Get the Timestamp of a test run

// Open SCTM database using SQL Enterprise Manager or equivalent
// Run the SQL query below against SCTM database

// In the "WHERE" clause, please specify the execution definition id you wish to search.

SilkCentral Test Manager 2009

SELECT SCC_Projects.ProjectName AS [Project Name], TM_ExecDefinitionRuns.ExecDefID_fk AS [Execution Definition ID],
TM_ManualTestResults.ChangedBy AS [User], SUM(TM_ExecutionDefinitions.PassedCount) AS [Passed Count],
SUM(TM_ExecutionDefinitions.FailedCount) AS [Failed Count], SUM(TM_ExecutionDefinitions.NotExecutedCount) AS [Not Executed Count],
SUM(TM_ExecutionDefinitions.PassedCount TM_ExecutionDefinitions.FailedCount) AS [Total Executed], SUM(TM_ExecutionDefinitions.OverallCount)
AS [Overall Count], CAST(TM_V_ExecDefinitions.LastRunAt AS CHAR) AS [Last Execution Timestamp], TM_V_ExecDefinitions.ProductCode AS Product,
TM_V_ExecDefinitions.BuildName AS Build, TM_V_ExecDefinitions.VersionName AS Version, TM_ExecDefinitionRuns.Keywords
FROM TM_TestDefExecutions INNER JOIN
TM_ManualTestResults ON TM_TestDefExecutions.TestDefExecID_pk_fk = TM_ManualTestResults.TestDefExecID_pk_fk INNER JOIN
TM_ExecDefinitionRuns ON TM_TestDefExecutions.ExecDefRunID_fk = TM_ExecDefinitionRuns.ExecDefRunID_pk_fk INNER JOIN
TM_ExecutionDefinitions ON TM_ExecDefinitionRuns.ExecDefID_fk = TM_ExecutionDefinitions.ExecDefID_pk_fk INNER JOIN
SCC_Projects ON TM_TestDefExecutions.ProjectID_fk = SCC_Projects.ProjectID_pk AND
TM_ExecDefinitionRuns.ProjectID_fk = SCC_Projects.ProjectID_pk INNER JOIN
TM_V_ExecDefinitions ON TM_ExecutionDefinitions.ExecDefID_pk_fk = TM_V_ExecDefinitions.ExecDefID_pk_fk
WHERE (TM_ExecDefinitionRuns.ExecDefID_fk LIKE "%")
GROUP BY TM_ManualTestResults.ChangedBy, TM_ExecDefinitionRuns.ExecDefID_fk, SCC_Projects.ProjectName, TM_V_ExecDefinitions.LastRunAt,
TM_V_ExecDefinitions.ProductCode, TM_V_ExecDefinitions.BuildName, TM_V_ExecDefinitions.VersionName, TM_ExecDefinitionRuns.Keywords

If you require the SCTM 2008 R2 SP1 SQL query, this is available in the article attachment. The difference between the SCTM 2008R2SP1 and SCTM 2009 queries is due to database schema changes.

Note: Please specify the execution definition id in the where clause.

Get Last Execution of an Execution ID.txt

Old KB# 30406
Comment List
Related Discussions