Level Extreme platform
Subscription
Corporate profile
Products & Services
Support
Legal
Français
Eliminating dups using multiple tables
Message
 
General information
Forum:
Visual FoxPro
Category:
Databases,Tables, Views, Indexing and SQL syntax
Environment versions
Visual FoxPro:
VFP 9 SP1
OS:
Windows XP
Database:
Visual FoxPro
Miscellaneous
Thread ID:
01128331
Message ID:
01128349
Views:
21
This seems to be much better:
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
Now I can see multiple UNKNOWN patients and also some person with date of birth 08/26/1864 <g>
If it's not broken, fix it until it is.


My Blog
Previous
Next
Reply
Map
View

Click here to load this message in the networking platform