title: Join a Query with another Query while using a common table expression
body: <p>I have the following 2 queries:</p>
  
  <p>Query 1:</p>
  
  <pre><code>WITH JobTransactionsSumTypes AS
  (
  SELECT 
        [Job],
        [Cost_Code],
        SUM(CASE WHEN [Transaction_Type] = 'AP cost' AND [Commitment] &lt;&gt;''        THEN [Amount]-[Noncosted_Tax] ELSE 0 END) AS APcostAmountComSum,
        SUM(CASE WHEN [Transaction_Type] = 'AP cost' AND [Commitment] =''     THEN [Amount]-[Noncosted_Tax] ELSE 0 END) AS APcostAmountNonComSum,
        SUM(CASE WHEN [Transaction_Type] = 'Approved est changes'             THEN [Amount]-[Noncosted_Tax] ELSE 0 END) AS ApprovedEstChangesAmountSum,
        SUM(CASE WHEN [Transaction_Type] = 'Aprvd cmmtt cst chng'             THEN [Amount]-[Noncosted_Tax] ELSE 0 END) AS AprvdCmmttCstChngAmountSum,
        SUM(CASE WHEN [Transaction_Type] = 'Aprvd schdl val chn'          THEN [Amount]-[Noncosted_Tax] ELSE 0 END) AS AprvdSchdlValChnAmountSum,
        SUM(CASE WHEN [Transaction_Type] = 'Cash receipt'                 THEN [Amount]-[Noncosted_Tax] ELSE 0 END) AS CashReceiptAmountSum,
        SUM(CASE WHEN [Transaction_Type] = 'Committed cost'           THEN [Amount]-[Noncosted_Tax] ELSE 0 END) AS CommittedCostAmountSum,
        SUM(CASE WHEN [Transaction_Type] = 'Dollars paid'                 THEN [Amount]-[Noncosted_Tax] ELSE 0 END) AS DollarsPaidAmountSum,
        SUM(CASE WHEN [Transaction_Type] = 'General Ledger only'          THEN [Amount]-[Noncosted_Tax] ELSE 0 END) AS GeneralLedgerOnlyAmountSum,
        SUM(CASE WHEN [Transaction_Type] = 'JC cost'              THEN [Amount]-[Noncosted_Tax] ELSE 0 END) AS JCcostAmountSum,
        SUM(CASE WHEN [Transaction_Type] = 'Misc worksheet 1'             THEN [Amount]-[Noncosted_Tax] ELSE 0 END) AS MiscWorksheet1AmountSum,
        SUM(CASE WHEN [Transaction_Type] = 'Misc worksheet 2'             THEN [Amount]-[Noncosted_Tax] ELSE 0 END) AS MiscWorksheet2AmountSum,
        SUM(CASE WHEN [Transaction_Type] = 'Misc worksheet 3'             THEN [Amount]-[Noncosted_Tax] ELSE 0 END) AS MiscWorksheet3AmountSum,
        SUM(CASE WHEN [Transaction_Type] = 'Misc worksheet 4'             THEN [Amount]-[Noncosted_Tax] ELSE 0 END) AS MiscWorksheet4AmountSum,
        SUM(CASE WHEN [Transaction_Type] = 'Original estimate'            THEN [Amount]-[Noncosted_Tax] ELSE 0 END) AS OriginalEstimateAmountSum,
        SUM(CASE WHEN [Transaction_Type] = 'Pending est changes'          THEN [Amount]-[Noncosted_Tax] ELSE 0 END) AS PendingEstChangesAmountSum,
        SUM(CASE WHEN [Transaction_Type] = 'Percent complete'             THEN [Amount]-[Noncosted_Tax] ELSE 0 END) AS PercentCompleteAmountSum,
        SUM(CASE WHEN [Transaction_Type] = 'Pndng cmmtt cst chng'             THEN [Amount]-[Noncosted_Tax] ELSE 0 END) AS PndngCmmttCstChngAmountSum,
        SUM(CASE WHEN [Transaction_Type] = 'Pndng schd val chg 2'         THEN [Amount]-[Noncosted_Tax] ELSE 0 END) AS PndngSchdValChg2AmountSum,
        SUM(CASE WHEN [Transaction_Type] = 'Pndng schd val chg 3'         THEN [Amount]-[Noncosted_Tax] ELSE 0 END) AS PndngSchdValChg3AmountSum,
        SUM(CASE WHEN [Transaction_Type] = 'Pndng schdl val chng'             THEN [Amount]-[Noncosted_Tax] ELSE 0 END) AS PndngSchdlValChngAmountSum,
        SUM(CASE WHEN [Transaction_Type] = 'PR cost'              THEN [Amount]-[Noncosted_Tax] ELSE 0 END) AS PRcostAmountSum,
        SUM(CASE WHEN [Transaction_Type] = 'Receivable Adjstment'             THEN [Amount]-[Noncosted_Tax] ELSE 0 END) AS ReceivableAdjstmentAmountSum,
        SUM(CASE WHEN [Transaction_Type] = 'Retention billed'             THEN [Amount]-[Noncosted_Tax] ELSE 0 END) AS RetentionBilledAmountSum,
        SUM(CASE WHEN [Transaction_Type] = 'Scheduled value'          THEN [Amount]-[Noncosted_Tax] ELSE 0 END) AS ScheduledValueAmountSum,
        SUM(CASE WHEN [Transaction_Type] = 'Work Billed'              THEN [Amount]-[Noncosted_Tax] ELSE 0 END) AS WorkBilledAmountSum
    FROM [ADCData_Doric].[dbo].[JCT_CURRENT__TRANSACTION]
    WHERE 
  GROUP BY
        [Job],
        [Cost_Code]
  )
  SELECT
      Job,
      Cost_Code,
      APcostAmountComSum,
      APcostAmountNonComSum,
      ApprovedEstChangesAmountSum,
      AprvdCmmttCstChngAmountSum,
      AprvdSchdlValChnAmountSum,
      CashReceiptAmountSum,
      CommittedCostAmountSum,
      DollarsPaidAmountSum,
      GeneralLedgerOnlyAmountSum,
      JCcostAmountSum,
      MiscWorksheet1AmountSum,
      MiscWorksheet2AmountSum,
      MiscWorksheet3AmountSum,
      MiscWorksheet4AmountSum,
      OriginalEstimateAmountSum,
      PendingEstChangesAmountSum,
      PercentCompleteAmountSum,
      PndngCmmttCstChngAmountSum,
      PndngSchdValChg2AmountSum,
      PndngSchdValChg3AmountSum,
      PndngSchdlValChngAmountSum,
      PRcostAmountSum,
      ReceivableAdjstmentAmountSum,
      RetentionBilledAmountSum,
      ScheduledValueAmountSum,
      WorkBilledAmountSum,
      (ScheduledValueAmountSum) AS StartContractAmount,
      (AprvdSchdlValChnAmountSum) AS ApprovedVariations,
    (OriginalEstimateAmountSum + ApprovedEstChangesAmountSum + PendingEstChangesAmountSum) AS TotalBudgetOriginalApprovedPending,
    (OriginalEstimateAmountSum + ApprovedEstChangesAmountSum)*(MiscWorksheet1AmountSum/100)+(PendingEstChangesAmountSum*(MiscWorksheet2AmountSum/100)) AS TotalCommitmentsBudget,
  (CommittedCostAmountSum + AprvdCmmttCstChngAmountSum + PndngCmmttCstChngAmountSum) AS ApprovedPendingCommitedCosts
      -- You Could Add additional Calculations here.
  FROM
      [JobTransactionsSumTypes]
  </code></pre>
  
  <p>Query 2:</p>
  
  <pre><code>SELECT
       APM_MASTER__DISTRIBUTION.Job
       , APM_MASTER__DISTRIBUTION.Cost_Code
       ,SUM(CASE WHEN APM_MASTER__DISTRIBUTION.Onhold_Status &lt;&gt;0                THEN JCT_CURRENT__TRANSACTION.Amount-JCT_CURRENT__TRANSACTION.Noncosted_Tax ELSE 0 END) AS OnHoldAmount
       ,SUM(CASE WHEN APM_MASTER__DISTRIBUTION.Onhold_Status =0                  THEN JCT_CURRENT__TRANSACTION.Amount-JCT_CURRENT__TRANSACTION.Noncosted_Tax ELSE 0 END) AS NotOnHoldAmount
  FROM
    dbo.JCT_CURRENT__TRANSACTION JCT_CURRENT__TRANSACTION
    LEFT OUTER JOIN dbo.APM_MASTER__DISTRIBUTION APM_MASTER__DISTRIBUTION
      ON JCT_CURRENT__TRANSACTION.Vendor = APM_MASTER__DISTRIBUTION.Vendor AND JCT_CURRENT__TRANSACTION.Invoice = APM_MASTER__DISTRIBUTION.Invoice AND JCT_CURRENT__TRANSACTION.Dist_Sequence = APM_MASTER__DISTRIBUTION.Dist_Seq
  GROUP BY
    APM_MASTER__DISTRIBUTION.Job
  , APM_MASTER__DISTRIBUTION.Cost_Code
  </code></pre>
  
  <p>I can join these in Crystal reports, but would like to move this into a single query through left outer joining (getting all Query 1 Results) and linking through the job and cost code to query 2.</p>
  
  <p>I suppose what I am asking is, Query 1 uses a common table expression but query 2 does not, and i'm not sure how to join them.</p>
  
  <p>Thanks.</p>