Tricky time-based filtering problem

[ookete]

I have a view with a datetime field and I need to eliminate rows that occur within 2 seconds of the first. So for example:

ID AcctID EntryTime
1 1 1/2/2010 10:00:00.000
2 1 1/2/2010 10:30:00.000
3 1 1/2/2010 10:30:00.500 *
4 1 1/2/2010 10:30:01.200 *
5 2 1/2/2010 10:30:01.000
6 2 1/2/2010 11:05:02.500

The (*) rows are the ones in question. I need to filter out ID 3 and 4 because they are within two seconds of ID 2, grouped by AcctID.

I would love to figure this out using set techniques instead of looping or cursors. Any thoughts?

 

[markros]

Start from blogspot.com/2010/02/refactoring-ranges.html
and perhaps this MSDN thread

http://social.msdn.microsoft.com/Fo...l/thread/b44dfa75-43b1-4ed8-b583-f4a3494e2cf4

can be of some help.

PluralSight Learning Library

 

[ookete]

Thank you. I think you left out the blogspot blog name in your link, the link gives me a 404.

 

[markros]

You're right, I copied it from the mentioned MSDN thread and perhaps didn't copy the full URL. It's

http://pratchev.blogspot.com/2010/02/refactoring-ranges.html

Though I've been thinking that your problem may be easier and all you need is a self-join based on RowNumber RowNumber+1.

In any case, reading this blog will be helpful.

PluralSight Learning Library

 

[markros]

See also another very similar problem

http://social.msdn.microsoft.com/Fo...l/thread/7f6f90b5-f4d8-4a7a-8619-83222b580fe0

PluralSight Learning Library