SQL Server 2016

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE #builds (
    Id INT IDENTITY(1,1) NOT NULL,
    StartTime INT,
    IsPassed BIT
)


INSERT INTO #builds (StartTime, IsPassed) VALUES
(1, 1),
(7, 1),
(10, 0),
(15, 1),
(21, 1),
(26, 0),
(34, 0),
(44, 0),
(51, 1),
(60, 1)
10 rows affected

WITH CTE AS (
  SELECT StartTime, IsPassed,
         LEAD(StartTime) OVER (ORDER BY StartTime) AS NextStartTime
  FROM #builds
),
CTE2 AS (
  SELECT StartTime, IsPassed, NextStartTime,
         CASE IsPassed WHEN 1 THEN 0 ELSE NextStartTime - StartTime END Duration,
         ROW_NUMBER() OVER (ORDER BY StartTime) -
         ROW_NUMBER() OVER (PARTITION BY IsPassed ORDER BY StartTime) AS grp
  FROM CTE
)
SELECT *
FROM CTE2
ORDER BY StartTime


StartTime
IsPassed
NextStartTime
Duration
grp


1
True
7
0
0

7
True
10
0
0

10
False
15
5
2

15
True
21
0
1

21
True
26
0
1

26
False
34
8
4

34
False
44
10
4

44
False
51
7
4

51
True
60
0
4

60
True
null
0
4


WITH CTE AS (
  SELECT StartTime, IsPassed,
         LEAD(StartTime) OVER (ORDER BY StartTime) AS NextStartTime
  FROM #builds
),
CTE2 AS (
  SELECT StartTime, IsPassed, NextStartTime,
         CASE IsPassed WHEN 1 THEN 0 ELSE NextStartTime - StartTime END Duration,
         ROW_NUMBER() OVER (ORDER BY StartTime) -
         ROW_NUMBER() OVER (PARTITION BY IsPassed ORDER BY StartTime) AS grp
  FROM CTE
)
SELECT MIN(StartTime) AS StartTime, SUM(Duration) AS Duration
FROM CTE2
WHERE IsPassed = 0
GROUP BY grp
ORDER BY MIN(StartTime)


StartTime
Duration


10
5

26
25

StartTime	IsPassed	NextStartTime	Duration	grp
1	True	7	0	0
7	True	10	0	0
10	False	15	5	2
15	True	21	0	1
21	True	26	0	1
26	False	34	8	4
34	False	44	10	4
44	False	51	7	4
51	True	60	0	4
60	True	null	0	4