select cPatients_pk, tEntered_Date, tLast_Updated_Date, Patients.dDOB, ; cSSN, cSex_Code, cntDups,; Names.cf_Name, Names.cl_Name, Names.cM_Initial, ; Address.cStreet1, Address.cCity, ; nvl(cAREA_CODE,space(3)) as cArea_Code, ; nvl(cEXCHANGE,space(3)) as cExchange, ; nvl(cLAST_FOUR, space(4)) as cLast_Four,; FROM (SELECT COUNT(*) AS cntDupsm, MAX(cPatients_pk) AS cPatients_pk, MAX(tEntered_Date) AS tEntered_Date, MAX(tLast_Updated_Date) AS tLast_Updated_Date, dDOB, cSSN, MAX(cSex_Code) AS cSex_Code; FROM Patients; GROUP BY cSSN,dDOB; WHERE NOT EMPTY(cSSn) AND NOT EMPTY(dDOB); HAVING cntDupsm > 1) cr; inner join Names on cr.cPatients_pk = Names.cPointer_fk ; inner join Address on cr.cPatients_pk = Address.cPointer_fk ; left join Phones on cr.cPatients_pk = Phones.cPointer_fk ; order by dDOB, cSSN, cSEX_Code,Names.cl_Name, Names.cf_Name; into cursor curDupPatients readwrite(not tested)
>select cPatients_pk, tEntered_Date, ; > tLast_Updated_Date, Patients.dDOB, ; > Patients.cSSN, Patients.cSex_Code, step1.cntDups from Patients ; > inner join ; > (select count(*) as cntDups, Patients.dDOB, Patients.cSSN ; > from Patients group by 2,3 having cntDups >1 where cSSN<>space(9) and dDOB<>{}) step1 ; > on Patients.dDOB = step1.dDOB and Patients.cSSN = step1.cSSN ; > order by Patients.dDOB, Patients.cSSN ; > into cursor curPossibleDups > index on cPatients_pk tag cPat_pk > set order to > > select cr.cPatients_pk, cr.tEntered_Date, ; > cr.tLast_Updated_Date, cr.dDOB, cr.cSSN, cr.cSex_Code, ; > Names.cf_Name, Names.cl_Name, Names.cM_Initial, ; > Address.cStreet1, Address.cCity, ; > nvl(cAREA_CODE,space(3)) as cArea_Code, ; > nvl(cEXCHANGE,space(3)) as cExchange, ; > nvl(cLAST_FOUR, space(4)) as cLast_Four, cntDups ; > from curPossibleDups cr inner join Names ; > on cr.cPatients_pk = Names.cPointer_fk ; > inner join Address ; > on cr.cPatients_pk = Address.cPointer_fk ; > left join Phones ; > on cr.cPatients_pk = Phones.cPointer_fk ; > order by 4,5,6,8,7 ; > into cursor curDupPatients readwrite>