Here's a solution using recursion, don't expect it to be quick though.

WITH Ordered AS (

SELECT m.MACHINE_NUMBER,m.VALUE,m.EVENT_DATE,

ROW_NUMBER() OVER(PARTITION BY m.MACHINE_NUMBER ORDER BY m.EVENT_DATE) AS rn,

ca.VALUE AS NextVALUE,

ca.EVENT_DATE AS NextEVENT_DATE

FROM #MACHINE_LOG m

OUTER APPLY(SELECT TOP 1 m2.VALUE,m2.EVENT_DATE

FROM #MACHINE_LOG m2

WHERE m2.MACHINE_NUMBER = m.MACHINE_NUMBER AND m2.EVENT_DATE > m.EVENT_DATE

AND DATEDIFF(second,m.EVENT_DATE,m2.EVENT_DATE) > @interval_miuntes

ORDER BY m2.EVENT_DATE) ca

),

Recur AS (

SELECT MACHINE_NUMBER,VALUE,EVENT_DATE,NextVALUE,NextEVENT_DATE

FROM Ordered

WHERE rn=1

UNION ALL

SELECT o.MACHINE_NUMBER,o.VALUE,o.EVENT_DATE,o.NextVALUE,o.NextEVENT_DATE

FROM Ordered o

INNER JOIN Recur r ON r.MACHINE_NUMBER = o.MACHINE_NUMBER

AND r.NextVALUE = o.VALUE

AND r.NextEVENT_DATE = o.EVENT_DATE

)

SELECT MACHINE_NUMBER,VALUE,EVENT_DATE

FROM Recur

ORDER BY MACHINE_NUMBER,EVENT_DATE;

____________________________________________________

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