-- 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_TESTCYCL_ID AS "Instance ID",
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"

--COUNT(DISTINCT STEP.ST_ID) AS "Number of Test Steps",
--COUNT(DISTINCT LT.LN_BUG_ID) AS "Number of Linked Defects",
--COUNT(DISTINCT BUG.BG_BUG_ID) AS "Number of Active Linked Defects"

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

--INNER JOIN STEP ON ST_RUN_ID = RN_RUN_ID
--LEFT JOIN LINK AS LT ON LN_ENTITY_ID = ST_ID AND LN_ENTITY_TYPE = 'STEP'

---- this filters for active defects
--LEFT JOIN
--(
--  SELECT BG_BUG_ID
--  FROM BUG WHERE BG_STATUS <> 'Closed' AND BG_STATUS <> 'Fixed'
--) BUG ON LN_BUG_ID = BG_BUG_ID

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

--GROUP BY RN_RUN_ID, RN_RUN_NAME,RN_EXECUTION_DATE, RN_EXECUTION_TIME, RN_DURATION, RN_STATUS, RN_ASSIGN_RCYC, RN_TESTER_NAME,
--TC_ACTUAL_TESTER,TC_PINNED_BASELINE,TC_EXEC_DATE,TC_EXEC_TIME,TC_PLAN_SCHEDULING_DATE,TC_PLAN_SCHEDULING_TIME,TC_TESTER_NAME,TC_STATUS,
--TC_ASSIGN_RCYC,TC_USER_01, CYCLE.CY_CYCLE, CAST (CYCLE.CY_COMMENT AS NVARCHAR(MAX)), --QC Memo fields must be converted to nvarchar
--CYCLE.CY_USER_01, CYCLE.CY_OPEN_DATE, CYCLE.CY_CLOSE_DATE, CYCLE.CY_REQUEST_ID, CYCLE.CY_STATUS, CYCLE.CY_PINNED_BASELINE, CYCLE.CY_CYCLE_ID, --CYCLE.CY_ASSIGN_RCYC, CYCLE.CY_VTS, TEST.TS_RESPONSIBLE, TEST.TS_USER_02, TEST.TS_USER_03, TEST.TS_USER_04,
--CAST (CYCLE.CY_USER_07 AS NVARCHAR(MAX)) --QC Memo fields must be converted to nvarchar

ORDER BY RUN.RN_RUN_ID