Generate a list of active users who haven't logged into the system in the last 60 days

Question

I'm trying to generate a list of active users who haven't logged into the system in the last 60 days. The table records all attempts so I need to filter by users who are Active (in the employee profile table) and don't have recent login entries in the login attempts table.

SELECT
         emp.full_name Name
       , emp.user_id   ID
       , emp.site_name SiteName
FROM     employee_profile_info_v       emp
JOIN     (
             SELECT
                      id
                    , MAX(log_in_date_time) Lastlog
             FROM     common_audit_login_attempts_t log
             WHERE    MAX(log_in_date_time) NOT BETWEEN GETDATE() AND DATEADD(DAY, -60, GETDATE())
             GROUP BY id
         )                             common_audit_login_attempts_t ON user_id = id
JOIN     common_audit_login_attempts_t log ON emp.user_id                       = log.id
ORDER BY User_ID DESC

Results in: "An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference."

Answer 1

You want NOT EXISTS. Your code looks like SQL Server, so this would look like:

select e.*
from employees e
where active = 1 and
      not exists (select 1
                  from common_audit_login_attempts_t cala
                  where la.user_id = e.id and
                        la.log_in_date_time > dateadd(day, -60, getdate())
                 );