Vlad,
Bellow is my current program. See, if you can improve the logic (for Address+days it works very slow, it works quite fast for two other cases):
function deduflag
lparameters deduby_arg, ndays_arg
local deduby, dbfname, prevtalk, prevdel, prevselec, prevorder, prevpos, ;
ndays, result
#include deduincl.h
deduby = iif(empty(m.deduby_arg), DDMM_SITUS, m.deduby_arg)
ndays = iif(empty(m.ndays_arg), DDNO_DAYS, m.ndays_arg)
dbfname = dbf()
if empty(m.dbfname)
wait window "DEDUFLAG: " + DDEM_NOBLD + "."
return DDEC_NOBLD
endif
if used(DDAL_QUERY)
wait window "DEDUFLAG: " + DDEM_INUSE + "."
return DDEC_INUSE
endif
prevtalk = set('talk')
prevdel = set('deleted')
set deleted on
prevselec = select(0)
result = 0
wait window nowait "Flagging potential dupes. Please wait..."
set talk on
do case
case m.deduby == DDMM_SITUS
select upper(ccode+town+street+str(stnum,4)+stnumext+unit) as situs, ;
count(*) as cntSitus;
from (m.dbfname) ;
group by 1 ;
having cntSitus>1 ;
order by 1 into cursor (DDAL_QUERY)
update (dbfname) set dedupeflag= DDFC_POSS ;
where upper(ccode+town+street+str(stnum,4)+stnumext+unit) ;
in (select situs from (DDAL_QUERY))
m.result=_tally
case m.deduby == DDMM_SITDA
select upper(ccode+town+street+str(stnum,4)+stnumext+unit) as situs, recnum, date ;
from (m.dbfname) ;
where dedupeflag=DDFC_NONE ;
into cursor myCursor order by recnum, situs, date
select myCursor.* ;
from myCursor inner join myCursor my on myCursor.recnum<>my.recnum;
and myCursor.situs=my.situs ;
and abs(myCursor.date - my.date) <= m.ndays ;
group by myCursor.recnum ;
order by 1 into cursor (DDAL_QUERY)
if m.result >= 0
result = reccount(DDAL_QUERY)
endif
if m.result > 0
result = 0
prevorder = order(m.prevselec)
prevpos = possave(m.prevselec)
set order to recnum in (m.prevselec)
set relation to recnum into (m.prevselec)
scan for not eof(m.prevselec)
replace dedupeflag with DDFC_POSS in (m.prevselec)
result = m.result + 1
endscan
set order to (m.prevorder) in (m.prevselec)
posrest(m.prevpos, m.prevselec)
endif
if used ('MyCursor')
use in myCursor
endif
case m.deduby == DDMM_MAPRF
select upper(ccode+town+Mapref) as ccttmap, ;
count(*) as cntMapref;
from (m.dbfname) ;
group by 1 ;
having cntMapref>1 ;
order by 1 into cursor (DDAL_QUERY)
update (dbfname) set dedupeflag= DDFC_POSS ;
where upper(ccode+town+Mapref);
in (select ccttmap from (DDAL_QUERY))
m.result=_tally
otherwise
wait window "DEDUFLAG: " + DDEM_UNKME + ": " + m.deduby
result = DDEC_UNKME
endcase
set talk &prevtalk
if used(DDAL_QUERY)
if m.result >= 0
wait window nowait "Flagged " + ltrim(str(m.result)) + " potential duplicates."
else
wait clear
endif
use in (DDAL_QUERY)
else
wait clear
endif
select (m.prevselec)
set deleted &prevdel
return m.result >Hi!
>
>>Nope, this would not work. Do you want me to explain why or can figure it by yourself? :)
>
> Well, expression should be (MT.ccode+MT.town+MT.street+str(MT.strnum,4)+...), right? I did not tried such king of queries, so will be glad to know exactly what is wrong ;)
>
>>
>>>Still the same idea ;))
>>>
>>>Update MyTable
>>> set flag='D'
>>> where MyTable.IDField IN
>>>(Select MT.IDField
>>> from MyTable MT
>>> where MT.MyDateField betwen m.ldDate - 10 AND m.ldDate + 10
>>> group by (ccode+town+street+str(strnum,4)+...), MT.IDField
>>> having count(MT.IDField)>1)
>>>
>>>I hope this will work, I'm not sure if we can use exression in GROUP BY list.
>>>
>>>
>>>>>Hi!
>>>>>
>>>>>The same idea. 'Where' will filter out records for count() function.
>>>>>
>>>>>Select IDField, count(IDField) as nCount
>>>>> from MyTable
>>>>> where MyDateField betwen m.ldDate - 10 AND m.ldDate + 10
>>>>> group by IDField
>>>>> having nCount>1
>>>>
>>>>Hi Vlad,
>>>>
>>>>Actually, I need to mark all duplicate records in myTable (set flag='D'), which have the same address (ccode+town+street+str(strnum,4)+...) and date within the range. How can I do this?
>>>>
>>>>My current solution is quite combersome, so I'm open for the suggestions.
>>>>
>>>>Thanks in advance.
>>>>>
>>>>>>>>I'm switching an index to a primary and am getting a duplicate index message. How can I find the duplicates amoung 1000's without
>>>>>>>>looking through them all?
>>>>>>>
>>>>>>>Select IDField, count(IDField) as nCount
>>>>>>> from MyTable
>>>>>>> group by IDField
>>>>>>> having nCount>1
>>>>>>
>>>>>>This question was asked so often, what it definitely should be in a FAQ.
>>>>>>
>>>>>>BTW, a little more complicated variation of this problem:
>>>>>>Find all duplicates, which have the same Id field and date within date specified +-10.
If it's not broken, fix it until it is.
My Blog 
