Try this, its a "Gaps and Islands" problem, you can find information about it in Jeff Modens excellent article here[/url]

WITH CTE AS (

SELECT [State_ID], [Value_Time], [Value],

ROW_NUMBER() OVER(ORDER BY [Value_Time]) AS rn1,

ROW_NUMBER() OVER(PARTITION BY [State_ID] ORDER BY [Value_Time]) AS rn2

FROM #tmp_GridResults_1)

SELECT [State_ID],

MIN([Value_Time]) AS MinDateTimeForState,

MAX([Value_Time]) AS MaxDateTimeForState

FROM CTE

GROUP BY [State_ID],rn2-rn1

ORDER BY MinDateTimeForState;

Good job of posting DDL and sample data, thanks.

____________________________________________________

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