R. Brush (7/31/2013)
Just a clarificaton:An alias for a calcuated column can be used in GROUP BY or ORDER BY clauses, but not in the SELECT column list as vega805 was attempting (at least not with SQL Server 2008).
Ex.
WITH test
AS (
SELECT 1 AS ID, 10 AS [rate], 1 AS [Hours]
UNION ALL
SELECT 2, 12, 2
UNION ALL
SELECT 3, 10, 4
UNION ALL
SELECT 4, 11, 6
)
Select [ID]
, [Rate] * [Hours] AS [Amount]
, [Amount] * (.15) as [Markup]
FROM test
Subsequently using the alias [Amount] to perform another calculation does generate an error in SQL Server 2008.
The syntax is slightly different and conversion from one SQL flavour to the other is trivial:
WITH test AS (
SELECT 1 AS ID, 10 AS [rate], 1 AS [Hours]
UNION ALL
SELECT 2, 12, 2
UNION ALL
SELECT 3, 10, 4
UNION ALL
SELECT 4, 11, 6
)
SELECT [ID]
, x.[Amount]
, x.[Amount] * (.15) AS [Markup]
FROM test
CROSS APPLY (SELECT [Rate] * [Hours] AS [Amount]) x
WITH test AS (
SELECT 1 AS ID, 10 AS [rate], 1 AS [Hours]
UNION ALL
SELECT 2, 12, 2
UNION ALL
SELECT 3, 10, 4
UNION ALL
SELECT 4, 11, 6
)
SELECT [ID]
, x.[Amount]
, y.[Markup]
FROM test
CROSS APPLY (SELECT [Rate] * [Hours] AS [Amount]) x
CROSS APPLY (SELECT x.[Amount] * (.15) AS [Markup]) y
ORDER BY y.[Markup] DESC
CROSS APPLY without a table reference equates to calculate.
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