SQL Server CASE Statement Evaluate Expression Once

I may be missing something obvious! Thanks in advance for any help.

I am trying to use a CASE statement in an inline SQL Statement. I only want to evaluate the expression once, so I am looking to put the expression in the CASE section, and then evaluate the result in each WHEN. Here is the example:

    SELECT
         MyTable.ColumnA, 
         CASE DateDiff(d, MyTable.MyDate, getDate())    
                WHEN <= 0 THEN 'bad'
                WHEN BETWEEN 1 AND 15 THEN 'reasonable'
                ELSE 'good'
         END as MyCalculatedColumn,
         MyTable.SomeOtherColumn

I know I can do this:

 CASE 
        WHEN DateDiff(d, MyTable.MyDate, getDate()) <= 0 THEN 'bad'
        WHEN DateDiff(d, MyTable.MyDate, getDate()) BETWEEN 1 AND 15 THEN 'reasonable'
        ELSE 'good'
 END

But in my first example, SQL does not seem to like this statement:

WHEN <= 0 THEN 'bad'

Note that the statement is inline with other SQL, so I can’t do something like:

DECLARE @DaysDiff bigint
SET @DaysDiff = DateDiff(d, MyTable.MyDate, getDate())  

 CASE @DaysDiff    
        WHEN <= 0 THEN 'bad'
        WHEN BETWEEN 1 AND 15 THEN 'reasonable'
        ELSE 'good'
 END

My actual DateDiff expression is much more complex and I only want to maintain its logic, and have it evaluated, only once.

Thanks again…

Answer

You can use apply for this purpose:

 SELECT MyTable.ColumnA, 
        (CASE WHEN day_diff <= 0 THEN 'bad'
              WHEN BETWEEN 1 AND 15 THEN 'reasonable'
              ELSE 'good'
         END) as MyCalculatedColumn,
        MyTable.SomeOtherColumn
FROM MyTable CROSS APPLY
     (VALUES (DateDiff(day, MyTable.MyDate, getDate()))) v(day_diff)

APPLY is a very handy way to add calculated values into a statement. Because they are defined in the FROM clause, they can be used in SELECT, WHERE, and GROUP BY clauses where column aliases would not be recognized.