Posted by Analizer1 on 01/18/08 15:51

thanks alot for the example..i'll be testing ...look somewhat what i
need...

thanks again

"Plamen Ratchev" <Plamen@SQLStudio.com> 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

• Next in forum: Re: Need Help Importing Data from Excel to My SQL Database
• Prev in forum: Re: New Release of Mogwai ERDesigner NG
• Thread view: Re: duplicate checking

[Reply to this message]