Sql Server - Joining subqueries using calculated fields

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 c开发者_运维百科alculated 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.

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 

If possible consider using a CTE as it makes it very easy. Something like this:

With Raw as
(
    SELECT TOP 11 C.Date,
    Avg(SettlementPri) As PriceY,
    Rank() OVER (ORDER BY C.Date desc) as dayrank
    FROM OTE C WHERE C.Comcode = 'C-'
    Group by C.Date
) 

select today.pricey as todayprice ,
yesterday.pricey as yesterdayprice,
(today.pricey - yesterday.pricey)/today.pricey * 100 as percentchange 
from Raw today
left outer join Raw yesterday on today.dayrank = yesterday.dayrank + 1

Obviously this doesn;t include the symbol but that can be included pretty easily. If using 'With' syntax doesn;t suit you can also use calculated fields with Outer Apply http://technet.microsoft.com/en-us/library/ms175156.aspx

Although the CTE will mean that you only need to write your price calculation once which is a lot cleaner

Cheers

I had the same problem and found this thread and found a solution so I thought I'd post it here.

Instead of using the column name as parameter for ON, copy the statement that gave you the colmun name in the first place:

replace:

ON dayrank_Today = C.dayrank_Yest

with:

ON (RANK() OVER (ORDER BY Date desc)) = C.dayrank_Yest 

Granted, you're displeasing the Programming Gods by violating DRY, but you could be pragmatic and mention the duplication in the comments, which should appease their wrath to a mild grumbling.