Q:

SQL Dynamic Column Reuse

I have the following SQL statement:
Select 
   DateAdd(month, 1, DateField) as MyNewDate, 
   CASE WHEN MyNewDate < GetDate() THEN 0 ELSE 1 END as Expired
End 

I would like to reuse the calculation in the DateAdd without reevaluating the DateAdd for the Expired column. In reality the query is a lot more complex than this simple dateAdd. 
The error I get is : 

Invalid column name 'MyNewDate'.

How can I reuse the dynamic column?

A:

You can't use an alias in the same query. 
You need something like this
SELECT MyNewDate, CASE WHEN MyNewDate < GetDate() THEN 0 ELSE 1 END as Expired
FROM
(
    Select DateAdd(month, 1, DateField) as MyNewDate...
) 

or retype it like
Select 
   DateAdd(month, 1, DateField) as MyNewDate, 
   CASE WHEN DateAdd(month, 1, DateField) < GetDate() THEN 0 ELSE 1 END as Expired
End 

