SQL: delete duplicate records if the difference between two duplicate records is less than 30 days

Question

I want to remove duplicate records from a table (duplicate based on fkInvoiceIdand fkcontractid) if the difference between two duplicate records is CreatedDate(another column)less than 30 days.

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;

Request above removes duplicate entries ( fkInvoiceIdand fkcontractid), but does not accept the value Date(createdDate).

Answer 1

Can you try this?

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;

Answer 2

Not knowing in which tables each column comes from ^{1 is a} little difficult to place the exact query that you need, but something in this direction should work:

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 ed, INNER.