Вопрос или проблема

  select R.Id, A.created_date from Rule R
    join Rule_Audit A on R.id=A.id
    where R.ID= (
      select top1 1 ID from Rule_Audit where Rule_Id=R.Rule_Id and created_date < dateadd 
       (MONTH, -7, GETDATE()) order by created_date desc
    )
  and R.status="Inactive"

WITH MostRecentAudit AS (
  SELECT 
    Rule_Id, 
    MAX(created_date) AS LastAuditDate
  FROM Rule_Audit
  WHERE Audit_Text="Status_Change"
  GROUP BY Rule_Id
)
SELECT R.Rule_Id, R.Status, A.LastAuditDate 
FROM Rule R
JOIN MostRecentAudit A ON R.Rule_Id = A.Rule_Id
WHERE R.Status="Inactive" 
AND A.LastAuditDate < DATEADD(MONTH, -6, GETDATE());

SELECT  R.Id, x.created_date
FROM    [RULE] R
CROSS APPLY (
        SELECT  TOP 1 A.*
        FROM    Rule_Audit A
        WHERE   A.id = R.id
        AND A.audit_text="status_change"
        ORDER BY A.created_date DESC
    ) x
WHERE   x.created_date < dateadd(MONTH, -7, GETDATE())
AND R.status="Inactive"

Ответ или решение

Запрос подзапроса для соответствия самой последней записи дочерней таблицы

В этой задаче мы хотим написать SQL-запрос для получения всех правил с неактивным статусом из таблицы Rule, и при этом учитывать только те аудиты, которые соответствуют последней записи с текстом "status_change" и были созданы более шести месяцев назад. Это важный критерий, так как мы не хотим включать в выборку записи, у которых соответствующие аудиты были созданы недавно.

Структура задачи

1. Выбор неактивных правил: Мы должны сначала отобрать записи из таблицы Rule, у которых статус "Inactive".

2. Поиск последних записей аудита: Нам нужно найти для каждого правила самую последнюю запись аудита, где текст аудита равен "status_change".

3. Проверка временного условия: Мы должны убедиться, что дата создания этой аудит записи старше шести месяцев от текущей даты.

SQL-запрос

Ниже представлен SQL-запрос, который соответствует указанным требованиям:

WITH MostRecentAudit AS (
  SELECT 
    Rule_Id, 
    MAX(created_date) AS LastAuditDate
  FROM Rule_Audit
  WHERE Audit_Text = 'status_change'
  GROUP BY Rule_Id
)

SELECT R.Rule_Id, R.Status, A.LastAuditDate 
FROM Rule R
JOIN MostRecentAudit A ON R.Rule_Id = A.Rule_Id
WHERE R.Status = 'Inactive' 
AND A.LastAuditDate < DATEADD(MONTH, -6, GETDATE());

Объяснение запроса

• CTE (Common Table Expression) MostRecentAudit:

  • Мы собираем все записи из таблицы Rule_Audit, отбираем только те, где текст аудита равен "status_change".
  • Используем MAX(created_date), чтобы получить дату последнего аудита по каждому Rule_Id.
  • Группируем результаты по Rule_Id, чтобы каждая строка содержала самую последнюю дату аудита для соответствующего правила.

• Основной запрос:

  • Мы выбираем все поля из таблицы Rule и присоединяем результат из CTE MostRecentAudit по Rule_Id.
  • Условие WHERE фильтрует неактивные правила и проверяет, чтобы последняя дата аудита была более шести месяцев назад, используя функцию DATEADD.

Альтернативный подход с использованием CROSS APPLY

Еще один вариант запроса, который также решает задачу, — использовать CROSS APPLY для получения последней записи аудита непосредственно для каждого правила:

SELECT R.Rule_Id, x.created_date
FROM Rule R
CROSS APPLY (
    SELECT TOP 1 A.*
    FROM Rule_Audit A
    WHERE A.Rule_Id = R.Rule_Id 
    AND A.Audit_Text = 'status_change'
    ORDER BY A.created_date DESC
) x
WHERE x.created_date < DATEADD(MONTH, -6, GETDATE())
AND R.Status = 'Inactive';

Заключение

В результате выполнения любого из этих запросов вы получите список правил с неактивным статусом, где последние записи аудита, относящиеся к изменению статуса, были созданы более чем шесть месяцев назад. Это помогает обеспечить актуальность статуса и избегает ошибок, связанных с недавними изменениями. Важно помнить, что поддержание высокой степени актуальности данных является критически важным аспектом для надежного функционирования систем управления данными.