title: Can I use a column calculated in a SQL Server view later on in that same view?
body: <p>Can I use a column calculated in a SQL Server view later on in that same view?</p>
  
  <p>Let's say I have the following view:</p>
  
  <pre><code>Select 
      t1.StartMile, t2.EndMile,  t2.EndMile- t1.StartMile as TotMile
  from 
     TableStarts as t1 
  inner join 
     TableEnds as t2 on t1.Id = t2.Id  
  </code></pre>
  
  <p>Is there a way to edit the view to do the following</p>
  
  <pre><code>Select  
      t1.StartMile, t2.EndMile, t2.EndMile - t1.StartMile as TotMile, 
      TotMile + 30  as EvenMoreMiles
  </code></pre>
  
  <p>I tried this and got error: </p>
  
  <blockquote>
    <p>Invalid column name 'TotMile'</p>
  </blockquote>
  
  <p>Please don't tell me to use <code>t2.EndMile - t1.StartMile + 30 as EvenMoreMiles</code>. <code>TotMiles</code> is a long case statement in my actual code. </p>
  
  <p>I rather not have to create an intermediate view. </p>
  
  <p>I am using SQL Server 2005.</p>
  
  <p>ADDED LATER</p>
  
  <p>Thanks for all the answers. I will upvote all. </p>
  
  <p>The answers raise the following new question:  </p>
  
  <p>Given that there are thousands of rows, and <code>TotMiles</code> looks like the following, which of the answers given would be the most efficient? Or would it be most efficient to create an intermediary view?</p>
  
  <pre><code>CASE WHEN t .TaskType = 1  and  t .StartTime &lt; '1/1/2012'  
                 THEN (tv.EndMile - tv.StartMile )  
       WHEN NOT (t .Location1_PKey = c.pkey OR t .Location2_PKey = c.pkey)
                then (tv.EndMile - tv.StartMile )
       WHEN (tv.EndMile - tv.StartMile ) &lt; 31  Then 0 
       ELSE  (tv.EndMile - tv.StartMile - 30 )
  END AS MilesAdjusted2012,
  </code></pre>