Calvin,

I realise this is a lot to ask but would you mind re-writing one of the "not in" clauses using a left join?  I have been trying to do it...

Ok, so how do I use not exists?  Where do I put the not and where do I put the exists?  I can't seem to get it to work.

Mike,

I realised yesterday that I should be getting an error if cascade delete is not on, but I am not.  I'm not sure why.  The child record does not get...

Ok, thanks for that.  Also part of the reason I wanted to pursue the script option rather than cascade deletes is because it may be very useful for future reference...

Thanks for that.  I used a cursor to do the same job (also taking out payroll).  I know cursors are not nice but how much difference is there between that...

Ok, it works.  I should have spotted that.  Sorry.

Thanks, I'm gonna test the whole thing now!

Hmm, I get "invalid column name 'sickid'"....

Ok this makes sense and I'd rather do this than add cascade delete.  I'll try to get it working and let you all know.  First problem though is that the...

Thanks for reminding me of that!  As a rule we do not use cascading deletes as we leave it up to the programmers to decide.  It's safer that way, but...

For a minute there I thought you were right!  You're right about the 2 scenarios - yes I do only need to do one de-dup.  But that leaves me with...

Ha ha, well kind of except I have other things to do than sit running de-dup scripts for the rest of my life!  Also this job needs to be scheduled...

The other thing I should mention is that there can be more than 2 of each record.  There can be any number of duplicates for each record.  (I say this...

Thanks so much for helping.....

The tables look like I mentioned in my first post.  A duplicate on Sickness is classed as one of two things:

1. Where payroll, staffid, start_date and...

If I do this:

Declare getfullrecords cursor local static For

 Select count (1), payroll, staff_id, start_date, end_date, sickid

   from sickness (nolock)

   group by payroll, staff_id, start_date, end_date, sickid having count(1)>1

instead of this:

Declare getfullrecords...

If I do this:

Declare getfullrecords cursor local static For

 Select count (1), payroll, staff_id, start_date, end_date, sickid

   from sickness (nolock)

   group by payroll, staff_id, start_date, end_date, sickid having count(1)>1

instead of this:

Declare getfullrecords...