Find timestamps near each other 1

[jhowe]

Working to clean up file registration/tracking, I am stumped on this one:

Table has a Registration_Id (Number) and Download_Date (Date/Time) for fields. When users get click-happy downloading files, two or more records are added to the table (with a 1-3 seconds later Date/Time value).

My task is to filter out this "bad behavior" for reporting. To return a recordset of bad file requests, I would like to create a query to find records that share the Registration_Id and have Download_Date values very close to each other.

Any help would, of course, be greatly appreciated.

 

[RickSpr]

Here's an SQL query that should do it:
SELECT Left.Registration_ID, Left.Download_Date, Right.Download_Date,
Abs(Left.Download_Date - Right.Download_Date) * 86400 AS Interval
FROM DownloadTable AS Left INNER JOIN DownloadTable AS Right
ON Left.Registration_ID = Right.Registration_ID
WHERE Abs(Left.Download_Date - Right.Download_Date) * 86400 < ###

For &quot;###&quot;, substitute a value that determines how close the downloads must be to qualify. The value is in seconds.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[jhowe]

Ah, the old self-join, technique. Works like a charm. Thanks Rick!

Joel Howe