Results 1 to 9 of 9
  1. #1
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10

    Unanswered: Finding records created within N minutes of each other

    I have the following generic table structure.
    Code:
    CREATE TABLE tableName (
       ...
     , datetime_created datetime DEFAULT GetDate()
     , created_by_user char(12)
    )
    I want to find out which records were created within a given period of time per user. For example I want to know if the user "georgev" (or any users) has created more than one record in this table within a 2 minute period.

    Hopefully thsi will warm your brains this cold Monday morning

    Any more information needed, let me know!
    George
    Home | Blog

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Using SQL Server 2000
    George
    Home | Blog

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Self join on user and b.datetime > a.datetime and b.datetime <= DATEADD(m, 2, a.datetime)

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    That assumes that you can't create two records at the same time. If you can then change to a between and a.pk <> b.pk.

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Thank you Mr Flump
    Code:
    SELECT a.datetime_created
         , a.created_by_user
    FROM   tableName a
     INNER
      JOIN tableName b
        ON a.created_by_user = b.created_by_user
       AND a.datetime_created > b.datetime_created
       AND a.datetime_created <= DateAdd(mi, 2, b.datetime_created)
    GROUP
        BY a.datetime_created
         , a.created_by_user
    George
    Home | Blog

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by pootle flump
    That assumes that you can't create two records at the same time. If you can then change to a between and a.pk <> b.pk.
    You can create 2 records at the same time...
    But I don't get why this won't work anyway?
    George
    Home | Blog

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Because records with matching times will not be returned (....AND a.datetime_created > b.datetime_created)

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I can't believe I didn't see that.

    Did I mention that I hate Mondays?

    Thanks again Mr Flump.
    George
    Home | Blog

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by georgev
    I can't believe I didn't see that.
    Nor me.

    My pleasure Mr. V.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •