Geek Answers Handbook

Sql Server - combining subqueries using computed fields

I am trying to calculate the percentage change in price between days. Since days are not consectutive, I embed in the request a calculated field that tells me what a relative day is (day 1, day 2, etc.). To compare today with yesterday, I offset the calculated day number by 1 in the subquery. what i want to do is combine the internal and external request on the estimated 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 to execute the query, I get an erro message indicating that dayrank_Today is an invalid column. I tried to rename it, qualify it, yell at it, and I get squats. Still a mistake.

+4
source share
3 answers

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

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

replace:

 ON dayrank_Today = C.dayrank_Yest

with:

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

Of course, you are unhappy with the Programming of the Gods violating DRY , but you can be pragmatic and mention duplication in the comments, which should calm their anger at the soft grunt.

+3
source

You cannot select a computed column and then use it in a join. You can use a CTE that I am not familiar with, or you can have tables select this way:

 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
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
+2
source

If possible, consider using CTE as it is very easy. Something like that:

 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 includes a symbol, but this can be easily turned on. If using the "C" syntax is not suitable, you can also use calculated fields using Outer Apply http://technet.microsoft.com/en-us/library/ms175156.aspx

Despite the fact that CTE will mean that you only need to write a price calculation that will be much cleaner

Greetings

+1
source

More articles:


All Articles