Error: The multi-part identifier could not be bound. when using a function and alias

You cannot pass parameters to functions across a JOIN.

http://stackoverflow.com/questions/4123413/the-multi-part-identifier-columnname-could-not-be-bound

Use APPLY:

SELECT ROW_NUMBER() OVER(ORDER BY x.Year DESC, x.Month) AS ID,
x.*,
fr.*
FROM (
SELECT Client,
Month(TimeStamp) As Month,
Year(TimeStamp) As Year,
Day(TimeStamp) As Day,
(dateadd(yy,(Year(TimeStamp)-1900),0)
+ dateadd(mm,Month(TimeStamp)-1,0)
+ Day(TimeStamp)-1) AS XTimeStamp,
Sum(KwTop) As KwTop,
Sum(KwHeap) as KwHeap,
Sum(KwLow) As KwLow
FROM Ori.vEnergyUnion
GROUP BY
Year(TimeStamp), Month(TimeStamp),Day(TimeStamp), Client
) x
OUTER APPLY
(
SELECT *
FROM Finance.fGetRates(x.XTimeStamp) Fr
WHERE fr.ValidFrom = x.XTimeStamp
) fr

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: