SQL Server 2017

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

CREATE TABLE test (id INT, date DATETIME, active INT);
INSERT INTO test VALUES
(1, '2021-08-08 12:05:17.817', 1),
(2, '2021-08-08 12:05:20.817', 1),
(3, '2021-08-08 12:05:23.817', 1),
(4, '2021-08-08 12:05:26.817', 1),
(5, '2021-08-08 12:05:29.817', 1),
(6, '2021-08-08 12:05:32.817', 1),
(7, '2021-08-08 12:05:35.817', 1),
(8, '2021-08-08 12:05:38.817', 1),
(9, '2021-08-08 12:05:41.817', 0),
(10, '2021-08-08 12:05:44.817', 0),
(11, '2021-08-08 12:05:47.817', 1),
(12, '2021-08-08 12:05:50.817', 1),
(13, '2021-08-08 12:05:53.817', 0),
(14, '2021-08-08 12:05:56.817', 0),
(15, '2021-08-08 12:05:59.817', 1),
(16, '2021-08-08 12:06:02.817', 0),
(17, '2021-08-08 12:06:05.817', 1),
(18, '2021-08-08 12:06:08.817', 1),
(19, '2021-08-08 12:06:11.817', 1),
(20, '2021-08-08 12:06:14.817', 0),
(21, '2021-08-08 12:06:17.817', 1),
(22, '2021-08-08 12:06:20.817', 1);
SELECT * FROM test;


id
date
active


1
2021-08-08 12:05:17.817
1

2
2021-08-08 12:05:20.817
1

3
2021-08-08 12:05:23.817
1

4
2021-08-08 12:05:26.817
1

5
2021-08-08 12:05:29.817
1

6
2021-08-08 12:05:32.817
1

7
2021-08-08 12:05:35.817
1

8
2021-08-08 12:05:38.817
1

9
2021-08-08 12:05:41.817
0

10
2021-08-08 12:05:44.817
0

11
2021-08-08 12:05:47.817
1

12
2021-08-08 12:05:50.817
1

13
2021-08-08 12:05:53.817
0

14
2021-08-08 12:05:56.817
0

15
2021-08-08 12:05:59.817
1

16
2021-08-08 12:06:02.817
0

17
2021-08-08 12:06:05.817
1

18
2021-08-08 12:06:08.817
1

19
2021-08-08 12:06:11.817
1

20
2021-08-08 12:06:14.817
0

21
2021-08-08 12:06:17.817
1

22
2021-08-08 12:06:20.817
1


-- How it works - prepare datd
WITH cte AS (
    SELECT *, 
           ROW_NUMBER() OVER (ORDER BY date) [Number in the whole array],
           ROW_NUMBER() OVER (PARTITION BY active ORDER BY date) [Numner in the rows with needed value],
           ROW_NUMBER() OVER (ORDER BY date) - ROW_NUMBER() OVER (PARTITION BY active ORDER BY date) [Group]
    FROM test
)
SELECT *
FROM cte
ORDER BY date


id
date
active
Number in the whole array
Numner in the rows with needed value
Group


1
2021-08-08 12:05:17.817
1
1
1
0

2
2021-08-08 12:05:20.817
1
2
2
0

3
2021-08-08 12:05:23.817
1
3
3
0

4
2021-08-08 12:05:26.817
1
4
4
0

5
2021-08-08 12:05:29.817
1
5
5
0

6
2021-08-08 12:05:32.817
1
6
6
0

7
2021-08-08 12:05:35.817
1
7
7
0

8
2021-08-08 12:05:38.817
1
8
8
0

9
2021-08-08 12:05:41.817
0
9
1
8

10
2021-08-08 12:05:44.817
0
10
2
8

11
2021-08-08 12:05:47.817
1
11
9
2

12
2021-08-08 12:05:50.817
1
12
10
2

13
2021-08-08 12:05:53.817
0
13
3
10

14
2021-08-08 12:05:56.817
0
14
4
10

15
2021-08-08 12:05:59.817
1
15
11
4

16
2021-08-08 12:06:02.817
0
16
5
11

17
2021-08-08 12:06:05.817
1
17
12
5

18
2021-08-08 12:06:08.817
1
18
13
5

19
2021-08-08 12:06:11.817
1
19
14
5

20
2021-08-08 12:06:14.817
0
20
6
14

21
2021-08-08 12:06:17.817
1
21
15
6

22
2021-08-08 12:06:20.817
1
22
16
6


WITH cte AS (
    SELECT *, 
           ROW_NUMBER() OVER (ORDER BY date) - ROW_NUMBER() OVER (PARTITION BY active ORDER BY date) [Group]
    FROM test
)
SELECT MIN(date) [From],
       MAX(date) [Till],
       COUNT(*) [Count]
FROM cte
WHERE active = 1
GROUP BY [Group]
ORDER BY [From]


From
Till
Count


2021-08-08 12:05:17.817
2021-08-08 12:05:38.817
8

2021-08-08 12:05:47.817
2021-08-08 12:05:50.817
2

2021-08-08 12:05:59.817
2021-08-08 12:05:59.817
1

2021-08-08 12:06:05.817
2021-08-08 12:06:11.817
3

2021-08-08 12:06:17.817
2021-08-08 12:06:20.817
2

id	date	active
1	2021-08-08 12:05:17.817	1
2	2021-08-08 12:05:20.817	1
3	2021-08-08 12:05:23.817	1
4	2021-08-08 12:05:26.817	1
5	2021-08-08 12:05:29.817	1
6	2021-08-08 12:05:32.817	1
7	2021-08-08 12:05:35.817	1
8	2021-08-08 12:05:38.817	1
9	2021-08-08 12:05:41.817	0
10	2021-08-08 12:05:44.817	0
11	2021-08-08 12:05:47.817	1
12	2021-08-08 12:05:50.817	1
13	2021-08-08 12:05:53.817	0
14	2021-08-08 12:05:56.817	0
15	2021-08-08 12:05:59.817	1
16	2021-08-08 12:06:02.817	0
17	2021-08-08 12:06:05.817	1
18	2021-08-08 12:06:08.817	1
19	2021-08-08 12:06:11.817	1
20	2021-08-08 12:06:14.817	0
21	2021-08-08 12:06:17.817	1
22	2021-08-08 12:06:20.817	1