WITH CTE AS (

SELECT *,

ROW_NUMBER() OVER(PARTITION BY [system_no] ORDER BY [event_date]) AS rn

FROM [event_history])

SELECT a.*

FROM CTE a

WHERE EXISTS(SELECT * FROM CTE b WHERE b.[system_no] = a.[system_no]

AND ((b.rn = a.rn + 1 AND b.[zonestate_id] = 'A' AND a.[zonestate_id] = 'ARM')

OR (b.rn = a.rn - 1 AND b.[zonestate_id] = 'ARM' AND a.[zonestate_id] = 'A')

))

____________________________________________________

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