Hi Hope you can help....

What would be the most efficient way to de dup a table
I have like 10 columns i need to Check against....

our business rule is any incomming information
can't be within today -90 , ie 90 days

a Example would be helpful
Thanks

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

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

thanks again

"Plamen Ratchev" <Plamen [at] SQLStudio.com> wrote in message
news:mbudne7pMdEw6hPanZ2dnUVZ_hWdnZ2d [at] 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