SQL Alias Column Name Needed In CASE WHEN Statement

Question

How can I reference the alias "New Date?" I keep getting an "Invalid column name 'NewDate'" error.

CASE
    WHEN p.Program IN ('program1') THEN (date_needed + 10)
    WHEN p.Program IN ('program2') THEN (date_needed + 20)
END AS [NewDate]

CASE 
    WHEN date_needed < [NewDate] THEN 'Program On Time' 
    ELSE 'Program Late'
END AS [Modified New Date]

Answer 1

Nest the CASE

SELECT
CASE 
    WHEN CASE
           WHEN p.Program IN ('program1') THEN (date_needed + 10)
           WHEN p.Program IN ('program2') THEN (date_needed + 20)
         END < [NewDate] THEN 'Program On Time' 
    ELSE 'Program Late'
END AS [Modified New Date]

Or use a sub-query, or CTE...

WITH CTE AS(
SELECT
CASE
    WHEN p.Program IN ('program1') THEN (date_needed + 10)
    WHEN p.Program IN ('program2') THEN (date_needed + 20)
END AS [NewDate], date_needed
FROM YourTable)

SELECT
CASE 
    WHEN date_needed < [NewDate] THEN 'Program On Time' 
    ELSE 'Program Late'
END AS [Modified New Date]
FROM CTE