Sql-server – How to find duration in a dataset

sql serversql-server-2008-r2t-sql

I have a dataset of the following structure:

Target, PollTime, Value
------------------------
1. A, 2013-12-13 13:31:44.000, 15.00
2. A, 2013-12-13 13:32:44.000, 16.00
3. A, 2013-12-13 13:33:44.000, 16.00
4. A, 2013-12-13 13:34:44.000, NULL
5. A, 2013-12-13 13:35:44.000, NULL
6. A, 2013-12-13 13:36:44.000, 15.00
7. B, 2013-12-21 05:29:34.000, 3.00
8. B, 2013-12-21 05:30:34.000, NULL
9. B, 2013-12-21 05:31:34.000, NULL
10. B, 2013-12-21 05:32:34.000, NULL
11. B, 2013-12-21 05:33:34.000, 4.00
12. B, 2013-12-21 05:34:34.000, NULL
13. B, 2013-12-21 05:35:34.000, NULL
14. B, 2013-12-21 05:36:34.000, 5.00

I would like to build a resultset that groups the null values, showing the start, end time and duration. (Null means the target was unavialble).

Target, StartDate, EndDate, Duration(min)
---------------------------------------------
1. A, 2013-12-13 13:34:44.000,2013-12-13 13:36:44.000, 2
2. B, 2013-12-21 05:30:34.000,2013-12-21 05:33:34.000 3
3. B, 2013-12-21 05:34:34.000,2013-12-21 05:36:34.000, 2

The StartDate would be the first NULL Value, while the EndDate would be the next NON-NULL Value, and my duration calcualtion would be based on those two values.

Any clues to build this query would be great.

As I am thinking of joining the table to itself, but I am not sure where to start.

Best Answer

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

Related Question