16

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
Taryn
  • 242,637
  • 56
  • 362
  • 405
tschock
  • 275
  • 2
  • 6
  • 8

2 Answers2

23

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;
Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
  • 1
    This is crazy to thing that there is no solution other than this. Having a specific statement to add condition on the resultset would be simple to create (I think). There is the `HAVING` for aggregate function, why not a `FILTER` for the resultset. Ok, this would be a third statement in a query, kind of messy but when you have a select with 20 column to write twice because of a simple condition, this is really messy and a pain in the *** to maintain. – AxelH Oct 27 '16 at 08:23
  • Ditto to AxelH's comments. It's 2008 and there should be a better way by now. – Earl F Glynn Sep 11 '18 at 17:29
8

In addition to Aaron's answer, you could use a common table expression:

;with cte_FreeDaysRemaining as
    (
        select
            frdFreedays - DateDiff(dd,conReceiptToStock,GetDate()) As FreeDaysRemaining
            --, more columns
        from yourtable
    )
    select
        FreeDaysRemaining
        --, more columns
    from cte_FreeDaysRemaining
    where FreeDaysRemaining <= @intFreeDays
Joey
  • 433
  • 2
  • 6