Post reply

Find all records entered more then 10 min apart

  • February 25, 2009 at 9:22 am

    #131399

    Hi, I would like to find example of records that are related by a PK that have data entries greater than 10 minutes apart ie, in the table below PK's 1, 3, 5, 6 have and Eventtime that occured greater than 10 minutes apart; however, PK's 2 & 4 have Eventtimes that occur less than 10 minutes apart. How would I be able to write a query that can go through every record in a table and find all instances where to 'Sold' events occur for the same customer greater than 10 minutes apart. Thanks in advance for any help.

    [p]

    PK Eventime Event

    1 2009-02-24 15:33:28.000 Sold

    1 2009-02-24 19:26:54.000 Sold

    2 2009-02-24 12:36:58.000 Sold

    2 2009-02-24 12:40:03.000 Sold

    3 2009-02-24 10:53:11.000 Sold

    3 2009-02-24 15:54:04.000 Sold

    4 2009-02-24 09:34:55.000 Sold

    4 2009-02-24 09:40:59.000 Sold

    5 2009-02-24 09:14:09.000 Sold

    5 2009-02-24 13:44:12.000 Sold

    6 2009-02-24 12:00:32.000 Sold

    6 2009-02-24 16:24:47.000 Sold

    [/p]

  • February 25, 2009 at 9:35 am

    #950101

    select PK

    from mytable

    where Event='Sold'

    group by PK

    having datediff(minute,min(Eventime),max(Eventime))>10

    ____________________________________________________

    Deja View - The strange feeling that somewhere, sometime you've optimised this query before

    How to get the best help on a forum

    http://www.sqlservercentral.com/articles/Best+Practices/61537

  • February 25, 2009 at 12:06 pm

    #950199

    Thanks Mark, that's what i was looking for...

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply