How Do I Create a New Column From a Column Newly Added by a Join?

Question

Using a left join, I have brought together data from two tables. From the data I brought in, I also created a new column using a logic statement (the AOIMonth column).

The question I have is, how do I create another column using another logic statement that references the new AOIMonth column? The AOIMonth column is not declared in a table, so how can I reference that column? Here's the code I have:

select c.*, b.[FirstMonth], b.[LastMonth], addmonths(b.[lastmonth], c.[Product AOI]) as AOIMonth

from [Churn Custom v4] c
left join (
        select[Line #1], min(Max_Month_Day) as [FirstMonth], max(Max_Month_Day) as 
        [LastMonth]
        from [Churn Custom v4]
        where [Invoiced_Flag]=1
        Group by [Line #1] ) b
    on c.[Line #1] = b.[Line #1]
order by c.[Max_Month_Day]

Basically, I want to say, "If AOIMonth = xyz, then abc else 0" Thanks!

Answer 1

You need to repeat the expression. You cannot re-use the alias, unless you use a subquery, CTE, or lateral join.

So:

(case when addmonths(b.[lastmonth], c.[Product AOI]) = xyz then abc else 0 end) as newcol