it looks like I can only post certain types of files. I posted the code instead. Sorry for the confusion.

Permitted file types: gif- bmp- jpe- jpeg- pdf- doc-...

thanks for the reply. I ended up using a recursive stored procedure. See attached and let me know if you have any comments. Thanks again

Thank you very much

Thank you.

Thank you for your reply. It was very helpful.

Another version. I think this will minimize the number of records returned from the join. I am wondering if there is a better way.

with CTE (ID, DT, RNK)

as (

select

a.ID,

a.DT,

row_number()...

This is what I currently have but it will have a performance issue as the source table might have millions records.

select

a.ID,

a.EfftDT,

isnull(a.TermDT,'9999-01-01')

from (

select

a.ID,

a.DT as EfftDT,

b.DT as TermDT,

row_number() over (partition...

Thanks for your replies.

If you look carefully, you will notice that row_number() or rank() will not work in this case. A recursion may work, but I just used a few...

Thanks Peso,

The new algorithm looks good, but I need to make sure There are no pure duplicates. If there are, it will delete them all.

INSERT @SAMPLE

SELECT 'A', 'B', 'C', 1...

Thanks Peso. You're brilliant. The codes look really great. I haven't fully tested it but I believe it will work.

Thanks a lot all others who participated. Have a good day!!

Thanks Peso,

Yes, The first line should be deleted. Thanks for your codes. I am still trying to understand it.

Thanks Steve,

I need to keep a record that has more informationl like RECID 1.

I need to keep both records in the example below.

RECID COL1...

Thanks Steve,

It should be a recurring process. Each week, I add about 3 million records to the big table but most of them are duplicates.

Yes, in the first example,...

thanks for your reply.

To answer Jeff's question, it's yes. The table contains about 1 billion records and about 10 differect columns (COL1 to COL10).

I'd like to delete the 4th record...

Thanks Jack,

I already removed the pure duplicates (same values in all the columns), so the basic rule is if record A has values (excluding 'blank') that all exists in another...