Enhance date calculations using the AND operator

Question

I'm sure this is a simple fix, but I can't find it anywhere. I'm trying to use AND to influence the results of a query. The query is as follows.

select first_name, log_in, date_opened, datediff(log_in, date_opened) as date_diff
from table1
where log_in < date_opened;

So the first query works fine. It only gives me rows where log_in date is less than date_opened and then the total days difference between the two, which is great. However, I want to add AND to the query to exclude certain totals. For example, the following example:

select first_name, log_in, date_opened, datediff(log_in, date_opened) as date_diff
from table1
where log_in < date_opened and date_diff > 1;

The problem is that the alias column date_diff is not recognized as a real column, so I get an error message. I tried using HAVING instead of AND, but that doesn't work (not what I thought). I basically want to exclude rows with zeros. Does anyone know what I would use instead of "and date_diff > 1"?

Answer 1

Repeat the expression in the where clause (i.e. use datediff(log_in, date_opened) > 1 instead of date_diff > 1), or use a derived table and then use Add conditions to the query.

Example of using derived table:

select first_name, log_in, date_opened, date_diff
from (
    select first_name, log_in, date_opened, datediff(log_in, date_opened) as date_diff
    from table1
    where log_in < date_opened
)
where date_diff > 1