You can assign a new name for a derived column in the SELECT statement which is also known as an alias name. But you need to understand how column alias are assigned and its scope
Let us create the following dataset
CREATE TABLE #temp (prod_id INT, transaction_Date DATETIME, amount DECIMAL(12,2)) INSERT INTO #temp SELECT 1,'2010-10-10',2700.00 UNION ALL SELECT 2,'2010-12-07',340 UNION ALL SELECT 3,'2011-09-03',1200.50 UNION ALL SELECT 2,'2011-08-11',4100 UNION ALL SELECT 1,'2012-01-01',17650.50
Suppose you want to find the year of the transaction date and display in the SELECT statement and also order by itself. You can use
SELECT YEAR(transaction_date) AS YEAR,amount FROM #temp ORDER BY YEAR
Which gives you the following result
year amount ----------- -------------- 2010 2700.00 2010 340.00 2011 4100.00 2011 1200.50 2012 17650.50
But what happens when you run the following?
SELECT YEAR(transaction_date) AS YEAR,amount FROM #temp WHERE YEAR=2010
You get an error
Msg 207, Level 16, State 1, Line 2
Invalid column name ‘year’.
It is because the column alias are not immediately known to the WHERE clause, whereas it is known in the ORDER BY clause because ORDER BY is executed lastly after the entire column list is known.
For more information about the order of execution in the statement, refer this my earlier blog.
Reference: Pinal Dave (https://blog.sqlauthority.com)