common table expression - SQL:Remove duplicate Records ,If Date Difference between duplicate records is less than 30 days -


i want delete duplicate records table (duplicate on basic of fkinvoiceid , fkcontractid) if createddate(another column) difference between 2 duplicate records less 30 days.

with cte  (        select          id,         fkcontractid,         fkinvoiceid,         createddate,          row_number() over(partition fkcontractid, fkinvoiceid order fkcontractid) [rn]        mytable left join invoice on invoice.pkinvoiceid = mytable.fkinvoiceid )    delete tt mytable tt inner join cte x on x.id = tt.id  x.[rn] > 1; 

above query deleting duplicate records(fkinvoiceid , fkcontractid) not taking difference of date(createddate).

can try this?

delete tt mytable tt inner join cte x on x.id = tt.id  x.[rn] > 1 , datediff(day,tt.date_col,x.date_col)<30; 

Comments