aaron.reese (5/7/2013)
@Lowell Good effort but not quite.Your results set picks up the recod with a time stamp of 41287 which is the first in a pair.
Same answer as above: a pair is defined as a discreet pair; i.e. if you are the second in a pair, you cannot be the first in the next pair even when the records are consecutive.
so
40820 and 41201 are a pair
41287 and 44797 are a pair
53698 and 60518 are a pair
Thanks. Try this:
SELECT *,
Filter = CASE WHEN seqno IS NULL
THEN ROW_NUMBER() OVER (PARTITION BY rnThingy ORDER BY date, time, seqno)
ELSE NULL END%2
FROM (
SELECT *,
rnThingy = CASE WHEN seqno IS NULL
THEN (ROW_NUMBER() OVER (ORDER BY date, time, seqno) - ROW_NUMBER() OVER (ORDER BY seqno, date, time))
ELSE NULL END
FROM #tmp
) d
ORDER BY date, time, seqno
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden