
April 6th, 2012, 11:31 AM
|
|
Registered User
|
|
Join Date: Apr 2012
Posts: 1
Time spent in forums: 21 m
Reputation Power: 0
|
|
|
Selecting entries by date which occur nearby
My problem is the following: I need to select records with the same name that have dates which occur within five days of each other. For example, if I had
Tim 5/3/06
Tim 5/5/06
Tim 11/2/06
Bill 1/13/07
Bill 1/20/07
Judy 7/4/06
Judy 7/7/06
my procedure should return
Tim 5/3/06
Tim 5/5/04
Judy 7/4/06
Judy 7/7/06
Any suggestions for doing this? I assume my best bet would be a cursor, though I'm unfamiliar with how to use a cursor to return this sort of data.
Here's the (woefully insufficient) query I came up with:
SELECT *
FROM tablename
and personname in (
SELECT personname
FROM tablename
GROUP BY personname HAVING DATEDIFF(day, min(date), max(date)) < 6 and DATEDIFF(day, min(date), max(date)) > 0
)
The problem is that this doesn't return the right answers when there are multiple dates associated with each person - for example, it would return Judy, but not Tim.
|