title: How do I avoid writing a SQL Server query twice to avoid duplication?
body: <p>I have an sql query that requires repeating the same <code>SELECT</code> statement a number of times within the overall query as input to other calculations.</p>
  
  <p>How can I write the query so that I only define the repeated <code>SELECT</code> SQL statement once? Is there a method for doing this to avoid making mistakes when it is repeated through the query?</p>
  
  <p>UPDATE: 
  in this instance the data returned from the SELECT is expected to be &lt; 100 rows in 80% of cases and max of 100-200 rows in the other 20% of cases</p>
  
  <p>CTE and table variables both look like valid answers - but how do you know which one is the more appropriate option in any situation?</p>