Posted by Plamen Ratchev on 09/30/75 12:00

Not sure I understand the requirements correctly, but here is an example of
removing duplicates based on multiple columns, within the last 90 days.
Requires SQL Server 2005.

CREATE TABLE Foo (
dcol1 INT,
dcol2 INT,
dcol3 INT,
ddate DATETIME);

INSERT INTO Foo VALUES (1, 2, 3, '20080101');
INSERT INTO Foo VALUES (1, 2, 3, '20070101');
INSERT INTO Foo VALUES (1, 2, 3, '20070101');
INSERT INTO Foo VALUES (1, 2, 3, '20070801');
INSERT INTO Foo VALUES (1, 2, 3, '20071101');
INSERT INTO Foo VALUES (1, 2, 3, '20071101');
INSERT INTO Foo VALUES (1, 2, 3, '20080101');
INSERT INTO Foo VALUES (2, 2, 3, '20080101');

WITH FooCTE
AS
( SELECT dcol1, dcol2, dcol3, ddate,
ROW_NUMBER() OVER(
PARTITION BY dcol1, dcol2, dcol3
ORDER BY ddate) AS rn
FROM Foo
WHERE ddate >= DATEADD(day, DATEDIFF(day, 0, CURRENT_TIMESTAMP), -90)
AND ddate < DATEADD(day, DATEDIFF(day, 0, CURRENT_TIMESTAMP), 1)
)
DELETE FROM FooCTE
WHERE rn > 1;

SELECT dcol1, dcol2, dcol3, ddate
FROM Foo;

HTH,

Plamen Ratchev
http://www.SQLStudio.com

• Next in forum: Re: Max(date) using joins
• Prev in forum: Re: Max(date) using joins
• Thread view: Re: duplicate checking

[Reply to this message]