Запрос там, где существует, а где не существует
Вот моя структура таблицы
Таблица - Пользователь
КОЛОНКИ - ПОЛЬЗОВАТЕЛЬ ~ СОЗДАНИЕ ~ ...
Таблица - Аудит
КОЛОНКИ - АУДИТИД ~ ОБЪЕКТ ~ ДЕЙСТВИЕ ~ ...
Пользователь для аудита - 1: N (от USERID до OBJECTID)
Я пытаюсь создать запрос, который возвращает всех пользователей, у которых есть запись аудита со значением действия 64 или 65 (в какой-то момент после последних 6 месяцев) или же, у которых вообще нет 64 или 65.
Вот что я придумал:
DECLARE @monthsInactiveFor INT
SET @monthsInactiveFor = -6
SELECT USR.fullname,
USRID.last_logged_in_date
FROM systemuser AS USR
INNER JOIN (SELECT DISTINCT AB.objectid AS USERID,
Max(AB.createdon) AS LAST_LOGGED_IN_DATE
FROM systemuserbase AS USR
LEFT OUTER JOIN auditbase AS AB
ON USR.systemuserid = AB.userid
WHERE ( AB.action = 64 )
OR ( AB.action = 65 )
GROUP BY AB.objectid
HAVING ( Max(AB.createdon) <=
Dateadd(month, @monthsInactiveFor,
Getdate()) )) AS USRID
ON USR.systemuserid = USRID.userid
WHERE ( USR.isdisabled = 0 )
AND ( USR.createdon <= Dateadd(month, @monthsInactiveFor, Getdate()) )
AND ( USR.accessmode = 0 )
ORDER BY USR.fullname
Вышеупомянутый запрос работает нормально и возвращает все записи, в которых есть строка таблицы аудита с действием 64 или 65, которые не были созданы за последние 6 месяцев.
Я изо всех сил пытаюсь найти тот же запрос для пользователей, у которых вообще нет записи аудита, со значением столбца ACTION 64 или 65.
Приветствуются любые указатели в правильном направлении.
Почему в таблице аудитов нет столбца даты? У одного пользователя может быть много аудитов, поэтому дата аудита является свойством аудита (а не дата создания строки пользователя).
Я также проверяю пользователей, которые были созданы недавно, до 6 месяцев, поскольку они, возможно, еще не вошли в систему. Следовательно, дата создания проверки строки пользователя поверх проверки даты создания аудита.
Ответы 2
Есть всего 3 логические группы людей:
- Люди, последнее событие аудита 64/65 которых произошло более 6 месяцев назад.
- Люди, последнее событие аудита 64/65 которых произошло менее 6 месяцев назад.
- Люди, у которых нет событий аудита 64/65
Вам нужны группы 1 и 3. Это все, кроме людей в группе 2. Так что измените логику: если максимальное количество созданных событий аудита приходится на последние 6 месяцев, то вам не нужен этот пользователь. Вы хотите всех остальных.
Если я использовал SELECT * FROM X WHERE NOT EXISTS (SELECT * FROM Y ON X.ID = Y.ID), не потеряю ли я доступ к столбцам в таблице Y? Точнее, в этом случае, время последнего входа в систему?
@dynamicallyCRM Сохраняет результат подзапроса USRID (кроме предложения Having) в табличной переменной. Это дает пользователю и время последнего входа в систему. Подзапрос Not exists может выбирать из этой таблицы переменную с помощью where LAST_LOGGED_IN_DATE <= Dateadd(month, @monthsInactiveFor, Getdate()). и основной запрос может независимо присоединяться к этой табличной переменной.
P.S. Я подозреваю, что DISTINCT может быть лишним; но без ваших данных сказать сложно.
Завершено выполнение UNION для получения всех записей, поскольку запрос также более читабелен, любые предложения по оптимизации приветствуются:
SELECT USR.FULLNAME,
AUDUSR.LAST_LOGGED_IN_DATE
FROM SYSTEMUSERBASE AS USR
INNER JOIN (SELECT DISTINCT AB.OBJECTID AS USERID,
MAX(AB.CREATEDON) AS LAST_LOGGED_IN_DATE
FROM SYSTEMUSERBASE AS USR
LEFT OUTER JOIN AUDITBASE AS AB
ON USR.SYSTEMUSERID = AB.USERID
WHERE ( AB.ACTION = 64 )
OR ( AB.ACTION = 65 )
GROUP BY AB.OBJECTID
HAVING ( MAX(AB.CREATEDON) <=
DATEADD(MONTH, @monthsInactiveFor,
GETDATE()) )
UNION
SELECT OBJECTID AS USERID,
NULL AS LAST_LOGGED_IN_DATE
FROM AUDITBASE
GROUP BY OBJECTID
HAVING MAX(CASE
WHEN ACTION = 64
OR ACTION = 65 THEN 1
ELSE 0
END) = 0) AS AUDUSR
ON USR.SYSTEMUSERID = AUDUSR.USERID
WHERE ( USR.ISDISABLED = 0 )
AND ( USR.CREATEDON <= DATEADD(MONTH, @monthsInactiveFor, GETDATE()) )
AND ( USR.ACCESSMODE = 0 )
ORDER BY AUDUSR.LAST_LOGGED_IN_DATE, USR.FULLNAME
Подсказка: попробуйте Union