How to get time difference Duration totals grouped by Name and when Value changes in certain way?

I’m looking for the Sum of time value differences between rows where Value was initially 1 and is now 0, grouped by Name.

Example data for a single Name, but there are many different Names in the real data.

+--------+---------------+---------------------+--------+
| RowNum |     Name      |      Timestamp      | Value  |
+--------+---------------+---------------------+--------+
|      1 | D1_HS_308_ALM | 2020-02-10 11:55:00 | 0      |
|      2 | D1_HS_308_ALM | 2020-02-10 11:51:00 | 0      |
|      3 | D1_HS_308_ALM | 2020-02-10 11:49:00 | NULL   |
|      4 | D1_HS_308_ALM | 2020-02-10 11:46:00 | 1      |
|      5 | D1_HS_308_ALM | 2020-02-10 08:02:00 | 0      |
|      6 | D1_HS_308_ALM | 2020-02-10 08:02:00 | NULL   |
|      7 | D1_HS_308_ALM | 2020-02-10 08:02:00 | 0      |
|      8 | D1_HS_308_ALM | 2020-02-10 07:56:00 | 1      |
|      9 | D1_HS_308_ALM | 2020-02-10 07:51:00 | 1      |
|     10 | D1_HS_308_ALM | 2020-02-10 07:50:00 | 0      |
+--------+---------------+---------------------+--------+

This data should return 11 minutes for Row 9–>7 transition and 5 minutes for Row 4–>2 transition; totaling 16 minutes for this Name.

I’m not sure how to accomplish this when it’s not guaranteed that the next row has the correct Value change – sometimes it will be several or many rows later.

Answer

DECLARE @myTable TABLE
(
    RowNum INT,
    Name VARCHAR(20),
    [Timestamp] DATETIME,
    Value INT
);

INSERT INTO @myTable
(
    RowNum,
    Name,
    [Timestamp],
    Value
)
VALUES
(1, 'D1_HS_308_ALM', '2020-02-10 11:55:00', 0),
(2, 'D1_HS_308_ALM', '2020-02-10 11:51:00', 0),
(3, 'D1_HS_308_ALM', '2020-02-10 11:49:00', NULL),
(4, 'D1_HS_308_ALM', '2020-02-10 11:46:00', 1),
(5, 'D1_HS_308_ALM', '2020-02-10 08:02:00', 0),
(6, 'D1_HS_308_ALM', '2020-02-10 08:02:00', NULL),
(7, 'D1_HS_308_ALM', '2020-02-10 08:02:00', 0),
(8, 'D1_HS_308_ALM', '2020-02-10 07:56:00', 1),
(9, 'D1_HS_308_ALM', '2020-02-10 07:51:00', 1),
(10, 'D1_HS_308_ALM', '2020-02-10 07:50:00', 0);

SELECT *,
       DATEDIFF(MINUTE, tStart, tEnd) AS duration
FROM
(
    SELECT t1.Name,
           MIN(t1.RowNum) AS rStart,
           MIN(t1.Timestamp) AS tStart,
           t.rNo AS rEnd,
           t.tEnd
    FROM @myTable t1
        OUTER APPLY
    (
        SELECT TOP (1)
               t2.RowNum,
               t2.Timestamp
        FROM @myTable t2
        WHERE t1.Name = t2.Name
              AND t2.Timestamp > t1.Timestamp
              AND t2.Value = 0
        ORDER BY t2.Timestamp
    ) t(rNo, tEnd)
    WHERE t1.Value = 1
    GROUP BY t1.Name,
             t.rNo,
             t.tEnd
) tmp;

Update:

SELECT Name,
       Sum(DATEDIFF(MINUTE, tStart, tEnd)) AS duration
FROM
(
    SELECT t1.Name,
           MIN(t1.Timestamp) AS tStart,
           t.tEnd
    FROM @myTable t1
        OUTER APPLY
    (
        SELECT TOP (1)
               t2.Timestamp
        FROM @myTable t2
        WHERE t1.Name = t2.Name
              AND t2.Timestamp > t1.Timestamp
              AND t2.Value = 0
        ORDER BY t2.Timestamp
    ) t(tEnd)
    WHERE t1.Value = 1
    GROUP BY t1.Name,
             t.tEnd
) tmp
group by name;