General information
Category:
Databases,Tables, Views, Indexing and SQL syntax
> Yes, I meant - my table has an adrress information (ccode+town+street+stnum+stnumext), which should be the same for dupes, and a date of transaction (field date), which for possible duplicates may vary in a particular range, specified by user. By default it's 7 day range.
>
> So, I may try something like:
> SELECT *, recno()...
I don't know why you need recno(), though it could come in handy for a related problem other than this one. It's good for offset self-joins, which you could use, for example, to return the number of days for each pair. But anyway...
...FROM mytable INNER JOIN mytable AS my ON mytable.pk=my.pk and mytable.address = my.address...
You don't need these in the join expression. The point is to join records with different keys on close dates:
...and between(mytable.date, my.date-7, my.date+7)
You might not need -7 and +7. Either one should find all the matches. I suppose that 7 will be replaced by a variable holding the user-selected value.
The refinements depend on the purpose of the query and definition of the problem. This query should match each record to every other record with a nearby date: if you have three records with datefields {^2000/04/05}, {^2000/04/07}, and {^2000/04/08} the first will join to the second and third, and the second will join to the third. If you use -7 and +7, each record will join to each of the others.
If you only want to join {^2000/04/05} with {^2000/04/07} and {^2000/04/07} with {^2000/04/08} and not join {^2000/04/05} with {^2000/04/08} you would use what I call an offset self-join. You would do something like this:
SELECT * FROM mytable INTO CURSOR mysorted ORDER BY mydate
SELECT *, RECNO() AS numrec FROM mysorted INTO CURSOR mynumbered
SELECT mysorted.whatever, mysorted.mydate - my2.mydate AS datediff FROM mysorted INNER JOIN mysorted AS my2 ON mysorted.numrec = my2.numrec - 1 WHERE mysorted.mydate >= my2.mydate - 7
You don't need "mysorted.mydate - my2.mydate AS datediff" in there but it might come in handy for something. All +, -, <, and > may require reversing in my examples - I often get those wrong.
>>if you have records for eight consecutive days? How many dupes is that? A SQL solution might involve some sort of non-equi-self-join, something like
>>
>>SELECT * FROM yourtable INNER JOIN yourtable AS yours1 ON yourtable.yourdate <= yours1.yourdate - 7
>>
Previous
Next
Reply
View the map of this thread
View the map of this thread starting from this message only
View all messages of this thread
View all messages of this thread starting from this message only