Building a complex SQL query
<p>I'm using MS SQL Server Management Studio. And I have 2 queries, that I need to merge into one.</p>

<p>First one is (you do not need to get the meaning of it, just skip to the next text):</p>

<pre><code>SELECT     TOP (100) PERCENT dbo.project.name AS Project_name, Test_Run.documentKey AS TestRun_Key, Test_Run.entitytype, Test_Run.documentKey, 
                  Test_Case.documentKey AS TestCase_Key, document_1.name AS TestPlan_Name, dbo.lookup.name AS TestCase_Release_Status, 
                  Test_Run.testRunStatus AS TestRun_Status, Test_Run.active, dbo.project.id AS Project_ID, dbo.version.versionNumber, Test_Run.executionDate AS Execution_Date, 
                  Test_Run.globalId AS TR_glID, Test_Case.globalId AS TC_glID, Test_Run.createdDate AS TR_Created_Date, Test_Case.id AS TestCase_ID, 
                  Test_Run.id AS TestRun_ID
FROM         dbo.[document] AS Test_Run INNER JOIN
                  dbo.project ON Test_Run.projectId = dbo.project.id INNER JOIN
                  dbo.[document] AS Test_Case ON Test_Run.testCaseId = Test_Case.id INNER JOIN
                  dbo.testrunset ON Test_Run.testRunSetId = dbo.testrunset.id INNER JOIN
                  dbo.[document] ON dbo.testrunset.testCycleId = dbo.[document].id INNER JOIN
                  dbo.[document] AS document_1 ON dbo.[document].testPlanId = document_1.id INNER JOIN
                  dbo.version ON Test_Run.testCaseVersionNumber = dbo.version.versionNumber AND Test_Run.testCaseId = dbo.version.originDocumentId INNER JOIN
                  dbo.documentcustomfieldvalue ON dbo.version.documentId = dbo.documentcustomfieldvalue.documentId INNER JOIN
                  dbo.lookup ON dbo.documentcustomfieldvalue.textValue = dbo.lookup.id
WHERE     (Test_Run.documentTypeId = 55) AND (dbo.documentcustomfieldvalue.fieldId = 216) AND (Test_Run.active = N'T') AND (Test_Case.testCaseStatusId &lt;&gt; 2)
</code></pre>

<p>ORDER BY TestCase_Key
And I have saved this query as a view <strong>MyView</strong> and using it in the following query:</p>

<pre><code>select TestCase_Key, TestRun_Status, Project_Name

from (select TestCase_Key, TestRun_Status, Project_Name,
         row_number() over(partition by TestCase_Key order by Execution_Date desc) as rn
  from MyView) as T

where rn = 1 and (Project_Name = 'TestProject')
    ORDER BY TestRun_Status, TestCase_Key
</code></pre>

<p>Now I need to merge these 2 queries, i.e. I am not allowed to use a view in my use case. Could someone help or give a hint? I have experimented whith "in" clause, but did not succeed.</p>