Hi everybody,

I have an application, which marks records by certain condition to be duplicates, and then allows users to make actions on them to resolve (Delete, Combine, Edit, etc.)

We can check for duplicates by address (combination of address fields) or by address within specified date range.

Now we change definition of duplicates. Only records with the same address and same price/mortgage amount will be considered as duplicates (and, I guess, always would be deleted additional records). So, for Address check I added Price, Mortgage into select statement, group by Address, Price, Mortgage and having count(*)>1

Here is the complicated code for Address+Date range check, which originally Vlad G helped me with. I'm not sure, how to provide check on Mortgage, Price in this check:

case m.DeduBy == DDMM_SITDA		&& match on Situs fields + date within range
* This case applies to Transaction only
* Firstly select all records and group by address, date
	select ;
		upper(ccode+town+street+str(stnum,4)+stnumext+unit) as situs, date, ;
		count(*) as SameDateDupl ;
		from (m.DbfName) ;
		&lcWhere ;
		group by 1,2 ;
		into cursor myCursor
	if _tally > 0 && There are duplicates in a file
** Prepare final cursor by self-join
		select myCursor.situs+dtos(myCursor.date) as SitusDate, myCursor.* ;
			from myCursor inner join myCursor my on myCursor.situs=my.situs ;
			and abs(myCursor.date - my.date) <= m.nDays ;
			and myCursor.date <> my.date ;
			union all ;
			select myCursor.situs+dtos(myCursor.date) as SitusDate, myCursor.* ;
			from myCursor ;
			where SameDateDupl>1 ;
			into cursor (DDAL_QUERY)
*	 index on SitusDate tag SitusDate
		use in select('myCursor')
		if _tally>0 && There are duplicates by Address + Date

* Then update the BldMstr table
			update (m.DbfName) set dedupeflag= DDFC_POSS ;
				where upper(ccode+town+street+str(stnum,4)+stnumext+unit)+dtos(date) ;
				in (select SitusDate from (DDAL_QUERY)) ;
				&lcWhereAnd
		endif
	endif
	Result=_tally && Number of updated records