Выводить только уникальные строки для заданного временного интервала

#sql-server #tsql

Вопрос:

Итак, я хочу иметь все, CHARGE где HPDATUM к следующему больше, чем 1 год назад.

Текущий запрос выглядит следующим образом:

SELECT DISTINCT CHARGE, HPDATUM FROM dbo.table123 WHERE CHARGE = '8811985' GROUP BY CHARGE, HPDATUM

Это пример запроса с ограничением where на CHARGE который будет удален в конечном запросе для получения всех CHARGE .

Это пример вывода приведенного выше запроса:

 CHARGE  HPDATUM

8811985  1999-10-08  | NEW ENTRY
8811985  2003-06-25  | NEW ENTRY
8811985  2003-06-26  | DUPLICATE TO THE ONE ABOVE
8811985  2004-09-04  | NEW ENTRY
8811985  2004-09-05  | DUPLICATE TO THE ONE ABOVE
8811985  2005-01-09  | DUPLICATE TOO

1. каков ожидаемый результат?

2. @Squirrel В списке выше те, где НОВАЯ ЗАПИСЬ отмечена мной вручную

3. этот список не может быть результатом запроса. В полученном вами запросе, DISTINCT CHARGE, HPDATUM но в списке вывода у вас есть дубликаты HPDATUM 2003-06-25

4. @Squirrel Извините, на самом деле 2003-06-26 в повторяющейся строке просто неправильно введено, я исправляю это сейчас

Ответ №1:

Мы можем попробовать использовать LEAD/LAG here для сравнения текущей HPDATUM даты с теми, которые идут до / следующими в последовательности, для каждой группы платежей.

 WITH cte AS (
    SELECT CHARGE, HPDATUM,
        LAG(HPDATUM, 1, DATEADD(year, -1, HPDATUM)) OVER (PARTITION BY CHARGE ORDER BY HPDATUM) HPDATUM_PREV,
        LEAD(HPDATUM, 1, DATEADD(year, 1, HPDATUM)) OVER (PARTITION BY CHARGE ORDER BY HPDATUM) HPDATUM_NEXT
    FROM dbo.table123
)

SELECT CHARGE, HPDATUM
FROM cte
WHERE
    DATEDIFF(year, HPDATUM, HPDATUM_PREV) >= 1 AND
    DATEDIFF(year, HPDATUM, HPDATUM_NEXT) >= 1;

Если вы хотите проверять только в одном направлении, то удалите одну из двух проверок опережения / задержки. Кроме того, если вы хотите видеть только один платеж, добавьте WHERE предложение к CTE.

1. Эй, ваша отредактированная версия приводит к неправильному синтаксису рядом с «ВЕДУЩИМ»

2. @Badgy Отсутствует запятая, извините. Я не проверял свой ответ.

3. Спасибо, ваша версия без предыдущей даты также уже работала хорошо, могу я спросить, почему вы тоже решили использовать предыдущую дату?

4. @Badgy Хороший вопрос, и вы тот, кто должен ответить на него. Приведенный выше ответ проверяет с обеих сторон каждой записи за определенную плату отсутствие смежных записей в течение одного года. Если вы хотите, вы также можете просто проверить в одном направлении.

Ответ №2:

используйте оконную функцию LAG (), чтобы получить предыдущее значение строки HPDATUM и сравнить

 CTE AS
(
    SELECT  t.CHARGE, t.HPDATUM,
            PREV_HPDATUM = LAG (t.HPDATUM) OVER (ORDER BY HPDATUM)
    FROM    dbo.table123 t
    WHERE   t.CHARGE = '8811985'
)
SELECT  CHARGE, HPDATUM
FROM    CTE
WHERE   PREV_HPDATUM    IS NULL
OR      HPDATUM     > DATEADD(YEAR, 1, PREV_HPDATUM)