R. Brush (8/1/2013)
ChrisM@Work,I know how to do it. I didn't say it could not be accomplished some other way.
My original post said that an alias cannot be referred to in a subsequent calculation like MS Access allows.
You chose to take issue with that, indicating I was giving misinformation in regards to later versions of SQL Server.
The fact is...an alias cannot be referred to in a subsequent calculation in the same way that MS Access allows. Period.
In the future, please don't say I'm wrong, if I'm not.
ChrisM@Work (7/31/2013)
R. Brush (7/31/2013)
Access lets you define a calculated column in a query and then use the calculated column name in other calculated columns. SQL Server will not allow this. ...Not in 7,2000, no. But it will in 2005 onwards.
CROSS APPLY lets you define a calculated column in a query and then use the calculated column name in other calculated columns.
You probably mean to say "Access allows you to perform a calculation in the output set (SELECT list) and reference the result of the calculation elsewhere in the output set". I'm ok with that. The original statement, however, is misleading and incorrect.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden