title: Sql Server - Joining subqueries using calculated fields
body: <p>I am trying to calculate the percentage change in price between days.  As the days are not consectutive, I build into the query a calculated field that tells me what relative day it is (day 1, day 2, etc).  In order to compare today with yesterday, I offset the calculated day number by 1 in a subquery.  what I want to do is to join the inner and outer query on the calculated relative day.  The code I came up with is:</p>
  
  <pre><code>SELECT TOP 11 
         P.Date,
         (AVG(P.SettlementPri) - PriceY) / PriceY as PriceChange, 
         P.Symbol,
         (RANK() OVER (ORDER BY P.Date desc)) as dayrank_Today
  FROM OTE P
    JOIN (SELECT TOP 11 
                 C.Date, 
                 AVG(SettlementPri) as PriceY, 
                 (RANK() OVER (ORDER BY C.Date desc))+1 as dayrank_Yest
            FROM OTE C
           WHERE C.ComCode = 'C-' 
        GROUP BY c.Date) C ON dayrank_Today = C.dayrank_Yest
  WHERE P.ComCode = 'C-' 
  GROUP BY P.Symbol, P.Date 
  </code></pre>
  
  <p>If I try and execute the query, I get an erro message indicating dayrank_Today is an invalid column.  I have tried renaming it, qualifying it, yell obsenities at it and I get squat. Still an error.</p>