#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2012
    Posts
    1
    Rep 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.
  2. #2
  3. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2012
    Posts
    1
    Rep Power
    0
    select p1.*
    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

IMN logo majestic logo threadwatch logo seochat tools logo