5 replaced http://dba.stackexchange.com/ with https://dba.stackexchange.com/
source | link

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: http://dba.stackexchange.com/questions/56593/getting-each-status-change-in-a-table/56594?noredirect=1#56594Getting each status change in a table

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: http://dba.stackexchange.com/questions/56593/getting-each-status-change-in-a-table/56594?noredirect=1#56594

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.
source | link

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 

Original source for code: http://dba.stackexchange.com/questions/56593/getting-each-status-change-in-a-table/56594?noredirect=1#56594

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 

Original source for code: http://dba.stackexchange.com/questions/56593/getting-each-status-change-in-a-table/56594?noredirect=1#56594

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: http://dba.stackexchange.com/questions/56593/getting-each-status-change-in-a-table/56594?noredirect=1#56594

3 Added corrected logic
source | link

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 

Original source for code: http://dba.stackexchange.com/questions/56593/getting-each-status-change-in-a-table/56594?noredirect=1#56594

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 

Original source for code: http://dba.stackexchange.com/questions/56593/getting-each-status-change-in-a-table/56594?noredirect=1#56594

2 improved formatting
source | link
1
source | link