Sql Server - Joining subqueries using calculated fields

Question

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:

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 

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.

Answer 1

You can't do a select of a calculated column, and then use it in a join. You can use CTEs, which I'm not so familiar with, or you can jsut do table selects like so:

SELECT 
     P.Date,       
     (AVG(AvgPrice) - C.PriceY) / C.PriceY as PriceChange, 
     P.Symbol,       
     P.dayrank_Today FROM
(SELECT TOP 11 
       ComCode,
       Date,
       AVG(SettlementPri) as AvgPrice,
       Symbol,
       (RANK() OVER (ORDER BY Date desc)) as dayrank_Today
FROM OTE WHERE ComCode = 'C-') 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 
GROUP BY P.Symbol, P.Date