SQL: удалить повторяющиеся записи, если разница в дате между повторяющимися записями составляет менее 30 дней

Question

Я хочу удалить дубликаты записей из таблицы (дубликаты на основе fkInvoiceId а также fkcontractid) если CreatedDate(another column) Разница между двумя дублирующимися записями составляет менее 30 дней.

WITH cte AS 
(   
    SELECT 
        Id,
        fkcontractid,
        fkInvoiceId,
        CreatedDate, 
        row_number() OVER(PARTITION BY fkcontractid, fkInvoiceId ORDER BY fkcontractid) AS [rn]   
    from mytable left join Invoice on Invoice.pkinvoiceid = mytable.fkinvoiceid
)   
delete tt from mytable tt inner join CTE as x on x.Id = tt.Id 
WHERE x.[rn] > 1;

Выше запрос удаляет дубликаты записей (fkInvoiceId а также fkcontractid) но это не принимает различие Date(createdDate),

Answer 1

Не зная, из каких таблиц в каждом столбце взят¹, немного сложно опубликовать точный запрос, но что-то вроде этого должно работать:

DELETE FROM t1
FROM mytable t1
    INNER JOIN
mytable t2
    ON
        t1.fkcontractid = t2.fkcontractid and
        t1.fkInvoiceId = t2.fkInvoiceId and
        t1.ID != t2.ID and --Assuming this is the primary key
        t1.CreatedDate < t2.CreatedDate and
        DATEADD(day,30,t1.CreatedDate) >= t2.CreatedDate

Надеюсь, логика достаточно ясна для чтения - для каждой строки мы пытаемся найти другую строку (IDне совпадают) с тем же fkcontractid а также fkInvoiceId, что происходит после текущей строки, но только на целых 30 дней.

Если нам удастся сделать это объединение, то этот ряд (из t1) должен быть удален.

---

¹ Если Invoice является важной частью определения этого запроса, мне неясно, почему он LEFT JOINскорее, чем INNERв твоей попытке.

Answer 2

Вы можете попробовать это?

delete tt from mytable tt inner join CTE as x on x.Id = tt.Id 
WHERE x.[rn] > 1 and datediff(day,tt.date_col,x.date_col)<30;