-- Test Execution

SELECT

RUN.RN_RUN_NAME AS "Run Name",
RUN.RN_RUN_ID AS "Run ID",
RUN.RN_EXECUTION_DATE AS "Run Execution Date",
RUN.RN_EXECUTION_TIME AS "RunExecution Time",
RUN.RN_DURATION AS "Run Duration",
RUN.RN_STATUS AS "Run Status",
RUN.RN_ASSIGN_RCYC AS "Run Target Cycle",
RCY_RN.RCYC_NAME AS "Run Target Cycle Name",
RUN.RN_TESTER_NAME AS "Run Tester Name",

TESTCYCL.TC_ACTUAL_TESTER AS "Instance Actual Tester",
TESTCYCL.TC_PINNED_BASELINE AS "Instance Baseline",
TESTCYCL.TC_EXEC_DATE AS "Instance Execution Date",
TESTCYCL.TC_EXEC_TIME AS "Instance Execution Time",
TESTCYCL.TC_PLAN_SCHEDULING_DATE AS "Instance Planned Exec Date",
TESTCYCL.TC_PLAN_SCHEDULING_TIME AS "Instance Planned Exec Time",
TESTCYCL.TC_TESTER_NAME AS "Instance Responsible Tester",
TESTCYCL.TC_STATUS AS "Instance Status",
TESTCYCL.TC_ASSIGN_RCYC AS "Instance Target Cycle",
RCY_TC.RCYC_NAME AS "Instance Target Cycle Name",
TESTCYCL.TC_USER_01 AS "Instance Responsible Tester Name",

CYCLE.CY_CYCLE AS "Test Set Name",
CYCLE.CY_CYCLE_ID AS "Test Set ID",
CYCLE.CY_USER_01 AS "Test Set Functional Area",
CONVERT(NVARCHAR(MAX),CYCLE.CY_COMMENT) AS "Test Set Description", --QC Memo fields must be converted to nvarchar
CYCLE.CY_ASSIGN_RCYC AS "Test Set Target Cycle ID",
RCY_CY.RCYC_NAME AS "Test Set Target Cycle Name",
CONVERT(NVARCHAR(MAX),CYCLE.CY_USER_07) AS "Test Set Full Folder Path", --QC Memo fields must be converted to nvarchar

TEST.TS_RESPONSIBLE AS "Plan Designer",
TEST.TS_USER_03 AS "Plan Business Value",
TEST.TS_USER_02 AS "Plan Complexity",
TEST.TS_USER_04 AS "Plan Functional Area"

FROM TESTCYCL

-- this restricts counts to last run execution date/time
LEFT JOIN
(
  SELECT
  RN_RUN_ID, RN_RUN_NAME, RN_EXECUTION_DATE, RN_EXECUTION_TIME, RN_DURATION, RN_STATUS,
  RN_ASSIGN_RCYC, RN_TESTER_NAME, RN_TESTCYCL_ID, ROW_NUMBER()
  OVER(PARTITION BY RN_TESTCYCL_ID ORDER BY RN_EXECUTION_DATE DESC,RN_EXECUTION_TIME DESC) AS LASTRUN
  FROM RUN
) RUN ON TC_TESTCYCL_ID = RN_TESTCYCL_ID

LEFT JOIN TEST ON TS_TEST_ID = TESTCYCL.TC_TEST_ID
LEFT JOIN CYCLE ON TESTCYCL.TC_CYCLE_ID = CYCLE.CY_CYCLE_ID
LEFT JOIN RELEASE_CYCLES RCY_CY ON RCY_CY.RCYC_ID = CY_ASSIGN_RCYC
LEFT JOIN RELEASE_CYCLES RCY_TC ON RCY_TC.RCYC_ID = TC_ASSIGN_RCYC
LEFT JOIN RELEASE_CYCLES RCY_RN ON RCY_RN.RCYC_ID = RN_ASSIGN_RCYC

-- this gets the last run execution
WHERE RUN.LASTRUN = 1 AND ISNULL(CAST(RCY_RN.RCYC_NAME as nvarchar(100)),'') LIKE  @Run_Release_Cycle_Name@

ORDER BY RUN.RN_RUN_ID