title: Optimize a Repeating Union All SSMS Query
body: <p>I'm creating a report that will get uploaded to QuickBooks. I have to UNION ALL 7 queries due to a single calculation change in each one. Below is an example of the first two queries:</p>
  
  <pre><code>SELECT
      Clients.Id
      ,@CNumber as 'Check Number'
      ,@TDate as 'Transac Date'
      ,CONCAT(@Memo, 'Debt') as 'Memo'
      ,Compensation.Payment as 'Amount'
  
  FROM Compensation
  
      JOIN Clients on Id = Compensation.ClientId
  
  UNION ALL
  
  SELECT
      Clients.Id
      ,@CNumber as 'Check Number'
      ,@TDate as 'Transac Date'
      ,CONCAT(@Memo, 'Insurance') as 'Memo'
      ,InsuranceInvoices.WeeklyAmount
  
  FROM InsuranceInvoices
  </code></pre>
  
  <p><strong>Is there any way to eliminate the need to repeat the Clients.Id, @CNumber, @TDate, CONCAT(@Memo, Variable) for every single union?</strong></p>
  
  <p>For the CONCAT(@MEMO, variable) as 'Memo", I can perhaps create an array and insert on each new iteration of Union ALL (don't know if that's even possible).</p>
  
  <p>Where I'm stuck is, not sure how to create a loop which iterates the first 4 columns, on each new calculation of 'Amount'.</p>
  
  <p>This is solely for the purposes of optimization/code readability. The query works and it's not all that slow. However, it's 155 lines long and I wonder if the next time I do something similar I can try doing so differently.</p>
  
  <p><strong>What I've researched so far</strong>: The following Stack Overflow <a href="https://stackoverflow.com/questions/15162670/optimize-union-all-sql">link</a> is somewhat similar, however, it's not using different 'FROM' Tables. The data is coming from a single table. My data is coming from 4 tables. Below a sample of two queries from the same table:</p>
  
  <pre><code>    SELECT
          Clients.Id
          ,@CNumber as 'Check Number'
          ,@TDate as 'Transac Date'
          ,CONCAT(@Memo, 'Debt') as 'Memo'
          ,Compensation.PaybackAmount as 'Amount'
  
      FROM Compensation
  
          JOIN Clients on Id = Compensation.ClientId
  
      WHERE
          Compensation.Week = @TrailingWeekEnd
  
  UNION ALL
  
      SELECT
          Clients.Id
          ,@CNumber as 'Check Number'
          ,@TDate as 'Transac Date'
          ,CONCAT(@Memo, 'Vehicle') as 'Memo'
          ,Compensation.VDAccumulation as 'Amount'
  
      FROM ContractorSettlements
  
          JOIN Clients on Id = Compensation.ClientId
  
      WHERE
          Compensation.Week = @TrailingWeekEnd
  </code></pre>
  
  <p>If this is a bad question, my apologies. In my mind, I imagine this working within a loop but I honestly don't know how to optimize it. If there is any more info I can provide, please let me know.</p>