sql server 2008 r2 - Referencing a calculated column in the where clause SQL

Question

This line of code is a snippet from my select statement.

frdFreedays - DateDiff(dd,conReceiptToStock,GetDate()) As FreeDaysRemaining

Below is a snippet from my where clause

and frdFreedays - DateDiff(dd,conReceiptToStock,GetDate()) <= @intFreeDays

The question I have is how can I reference the FreeDaysRemaining column and so I can compare it to @intFreeDays

I am looking for something like this

Freedays <= @intFreeDays

Answer 1

You can't reference an alias anywhere except ORDER BY. One workaround (aside from the obvious possibility of repeating the expression) is to put it in a derived table:

SELECT FreeDaysRemaining --, other columns
FROM
(
  SELECT frdFreedays - DATEDIFF(DAY, conReceiptToStock, GETDATE()) AS FreeDaysRemaining
    --, other columns
  FROM ...
) AS x
WHERE FreeDaysRemaining <= @intFreeDays;