Sql Query to find A series of dates that occur within 5 minutes of each other?

Question

This is Sql Server 2008. I've got a set of data that looks something like this:

Table UserAchievement
   id
   userId
   achievementId
   completedDate

When a user earns an award, the award and user is logged along with a date. What I'd like is a query that finds a set of 3 achievements that were earned within 5 minutes of each other by the same user. Any thoughts on how to accomplish this?

Thanks In Advance-

Chu

Answer 1

This article in my blog explains the solution in more detail:

• Finding sets within a time frame

---

SELECT  *
FROM    UserArchievement uf
WHERE   EXISTS
        (
        SELECT  NULL
        FROM    UserArchievement un
        WHERE   un.userId = uf.userID
                AND un.completedDate BETWEEN DATEADD(minute, -5, uf.completedDate) AND DATEADD(minute, 5, uf.completedDate)
                AND un.id <> uf.id
        )

If you want to select all sets of N awards within 5 minutes, use this:

SELECT  DISTINCT ue.id
FROM    UserArchievement uf
JOIN    UserArchievement ue
ON      ue.userID = uf.userID
        AND ue.completedDate BETWEEN uf.completedDate AND DATEADD(minute, 5, uf.completedDate)
WHERE   (
        SELECT  COUNT(*)
        FROM    UserArchievement un
        WHERE   un.userId = uf.userID
                AND un.completedDate BETWEEN uf.completedDate AND DATEADD(minute, 5, uf.completedDate)
        ) = 3

Replace 3 with any number of awards in the set you need.

Answer 2

OK, I got it -

I joined 3 tables back to my main table, each time using the between function to make sure the newly joined table was within 5 minutes of the original time.