No, it's not enough to de-dup on SSN + dDOB. We found cases there different patients have the same SSN & dDOB (perhaps, Identity theft cases, but it's not our job to report them). That's why I want to use a complex query. Also we may have situation when we changed address or added phone for patients. I want to preserve these records, even if it means we would not have them all properly de-duped. So, I want to save as much info as possible and de-dup based on the complex criteria rather than just SSN and DOB.
>>Now I want to to somehow de-dup these patients. First of all, what would be a logical assumption to de-dup? Leave the Patients, that have more transaction records associated with them? Or leave the patients with the newest cEntered_Date? Say, I want to use the second criterion. How can I identify these Patients? May be I can change my original query to include cPatients_pk? But how would I use the latest cEntered_Date?


>>
>>Once I select the master patients_pk, I still need all other patients PK, so I can re-point transaction records to the master PK. I can delete records in Address, Names, Phones, that point to other PKs after I re-point Transaction records.
>>
>>Could you please suggest some ideas? Should I try to scan this curDupPatients and try to populate cPatients_pk cursor?
>>
>>Thanks in advance.
>___________________________________
>Hi Nadya,
>
>The most logical to me is to de-dup on Social Security Number (and I assume that no two persons can have the same)
>
>Once you have identified those you can alter the ParentKey of all levels just below your patient table (say this is level 2). If the PatientPk is on levels 3 or more, a trigger with cascaded update may help
>
>The obsolete Patients could be deleted then
>
>Just a thought
>
>
>*** make a good backup first ***