April 6th, 2012, 12:31 PM
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
my procedure should return
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:
and personname in (
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.
April 26th, 2012, 07:10 AM
from prob01 as p1, prob01 as p2
where p1.name = p2.name
and p1.sampledate <> p2.sampledate
and datediff( dd, p1.sampledate, p2.sampledate) < 5
and datediff( dd, p2.sampledate, p1.sampledate) < 5