5 replaced http://dba.stackexchange.com/ with https://dba.stackexchange.com/ edited Apr 13 '17 at 12:42 As pointed out, this is better solved by Gaps and Islands. ``````WITH T AS (SELECT *, DENSE_RANK() OVER (PARTITION BY Target ORDER BY PollTime) - DENSE_RANK() OVER (PARTITION BY Target, Value ORDER BY PollTime) AS Grp FROM [schema].[Table] ) SELECT Target, MIN(PollTime) AS Start, MAX(PollTime) AS Finish, DATEDIFF(mi, MIN(PollTime), MAX(PollTime)) AS Duration FROM T GROUP BY Target, Grp HAVING MAX(Value) is null ORDER BY Start `````` As pointed out, this is better solved by Gaps and Islands. ``````WITH T AS (SELECT *, DENSE_RANK() OVER (PARTITION BY Target ORDER BY PollTime) - DENSE_RANK() OVER (PARTITION BY Target, Value ORDER BY PollTime) AS Grp FROM [schema].[Table] ) SELECT Target, MIN(PollTime) AS Start, MAX(PollTime) AS Finish, DATEDIFF(mi, MIN(PollTime), MAX(PollTime)) AS Duration FROM T GROUP BY Target, Grp HAVING MAX(Value) is null ORDER BY Start `````` As pointed out, this is better solved by Gaps and Islands. ``````WITH T AS (SELECT *, DENSE_RANK() OVER (PARTITION BY Target ORDER BY PollTime) - DENSE_RANK() OVER (PARTITION BY Target, Value ORDER BY PollTime) AS Grp FROM [schema].[Table] ) SELECT Target, MIN(PollTime) AS Start, MAX(PollTime) AS Finish, DATEDIFF(mi, MIN(PollTime), MAX(PollTime)) AS Duration FROM T GROUP BY Target, Grp HAVING MAX(Value) is null ORDER BY Start `````` Original source for code: Getting each status change in a table 4 `WHERE Value IS NULL` in the CTE breaks the "gaps and islands" construct. Filter out the unwanted groups in the outer query. edit approved May 14 '14 at 21:49 SQLFox 1,5241212 silver badges2323 bronze badges `GROUP BY` for the win: ``````SELECT [target] , MIN(polltime) AS startdate , MAX(polltime) AS enddate , DATEDIFF(mi, MIN(polltime), MAX(polltime)) AS duration FROM [schema].[table] WHERE value IS NULL GROUP BY [target]; `````` EDIT As pointed out, this is better solved by Gaps and Islands. ``````WITH T AS (SELECT *, DENSE_RANK() OVER (PARTITION BY Target ORDER BY PollTime) - DENSE_RANK() OVER (PARTITION BY Target, StatusValue ORDER BY PollTime) AS Grp FROM [schema].[Table] WHERE value IS NULL ) SELECT Target, MIN(PollTime) AS Start, MAX(PollTime) AS Finish, DATEDIFF(mi, MIN(PollTime), MAX(PollTime)) AS Duration FROM T GROUP BY Target, Grp HAVING MAX(Value) is null ORDER BY Start `````` `GROUP BY` for the win: ``````SELECT [target] , MIN(polltime) AS startdate , MAX(polltime) AS enddate , DATEDIFF(mi, MIN(polltime), MAX(polltime)) AS duration FROM [schema].[table] WHERE value IS NULL GROUP BY [target]; `````` EDIT As pointed out, this is better solved by Gaps and Islands. ``````WITH T AS (SELECT *, DENSE_RANK() OVER (PARTITION BY Target ORDER BY PollTime) - DENSE_RANK() OVER (PARTITION BY Target, Status ORDER BY PollTime) AS Grp FROM [schema].[Table] WHERE value IS NULL ) SELECT Target, MIN(PollTime) AS Start, MAX(PollTime) AS Finish, DATEDIFF(mi, MIN(PollTime), MAX(PollTime)) AS Duration FROM T GROUP BY Target, Grp ORDER BY Start `````` As pointed out, this is better solved by Gaps and Islands. ``````WITH T AS (SELECT *, DENSE_RANK() OVER (PARTITION BY Target ORDER BY PollTime) - DENSE_RANK() OVER (PARTITION BY Target, Value ORDER BY PollTime) AS Grp FROM [schema].[Table] ) SELECT Target, MIN(PollTime) AS Start, MAX(PollTime) AS Finish, DATEDIFF(mi, MIN(PollTime), MAX(PollTime)) AS Duration FROM T GROUP BY Target, Grp HAVING MAX(Value) is null ORDER BY Start `````` 3 Added corrected logic edited May 14 '14 at 19:26 Valkyrie 60155 silver badges1414 bronze badges `GROUP BY` for the win: ``````SELECT [target] , MIN(polltime) AS startdate , MAX(polltime) AS enddate , DATEDIFF(mi, MIN(polltime), MAX(polltime)) AS duration FROM [schema].[table] WHERE value IS NULL GROUP BY [target]; `````` EDIT As pointed out, this is better solved by Gaps and Islands. ``````WITH T AS (SELECT *, DENSE_RANK() OVER (PARTITION BY Target ORDER BY PollTime) - DENSE_RANK() OVER (PARTITION BY Target, Status ORDER BY PollTime) AS Grp FROM [schema].[Table] WHERE value IS NULL ) SELECT Target, MIN(PollTime) AS Start, MAX(PollTime) AS Finish, DATEDIFF(mi, MIN(PollTime), MAX(PollTime)) AS Duration FROM T GROUP BY Target, Grp ORDER BY Start `````` `GROUP BY` for the win: ``````SELECT [target] , MIN(polltime) AS startdate , MAX(polltime) AS enddate , DATEDIFF(mi, MIN(polltime), MAX(polltime)) AS duration FROM [schema].[table] WHERE value IS NULL GROUP BY [target]; `````` `GROUP BY` for the win: ``````SELECT [target] , MIN(polltime) AS startdate , MAX(polltime) AS enddate , DATEDIFF(mi, MIN(polltime), MAX(polltime)) AS duration FROM [schema].[table] WHERE value IS NULL GROUP BY [target]; `````` EDIT As pointed out, this is better solved by Gaps and Islands. ``````WITH T AS (SELECT *, DENSE_RANK() OVER (PARTITION BY Target ORDER BY PollTime) - DENSE_RANK() OVER (PARTITION BY Target, Status ORDER BY PollTime) AS Grp FROM [schema].[Table] WHERE value IS NULL ) SELECT Target, MIN(PollTime) AS Start, MAX(PollTime) AS Finish, DATEDIFF(mi, MIN(PollTime), MAX(PollTime)) AS Duration FROM T GROUP BY Target, Grp ORDER BY Start `````` 2 improved formatting edited May 14 '14 at 18:56 Max Vernon 59.7k1313 gold badges125125 silver badges253253 bronze badges 1 answered May 14 '14 at 18:52 Valkyrie 60155 silver badges1414 bronze badges