Re: duplicate checking
am 16.01.2008 22:31:15 von Plamen Ratchev
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
Re: duplicate checking
am 18.01.2008 16:51:38 von Analizer1
thanks alot for the example..i'll be testing ...look somewhat what i
need...
thanks again
"Plamen Ratchev" wrote in message
news:mbudne7pMdEw6hPanZ2dnUVZ_hWdnZ2d@speakeasy.net...
> 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