SQL Server 2019

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

CREATE TABLE dbo.z_test_duration
(
    [Days] date NOT NULL,
    [Status] char(8) NOT NULL
);
INSERT dbo.z_test_duration
    ([Days], [Status])
VALUES
    ('1/1/2022', 'on'),
    ('1/2/2022', 'on'),
    ('1/3/2022', 'on'),
    ('1/4/2022', 'off'),
    ('1/5/2022', 'on'),
    ('1/6/2022', 'off'),
    ('1/7/2022', 'on'),
    ('1/8/2022', 'on'),
    ('1/9/2022', 'off');
9 rows affected

SELECT 
    Z.*, 
    Seq = Z.[Days], -- ordering column
    rn = ROW_NUMBER() OVER (ORDER BY Z.[Days]) -- numbering
FROM dbo.z_test_duration AS Z
WHERE Z.[Status] = 'on';


Days
Status
Seq
rn


2022-01-01
on
2022-01-01
1

2022-01-02
on
2022-01-02
2

2022-01-03
on
2022-01-03
3

2022-01-05
on
2022-01-05
4

2022-01-07
on
2022-01-07
5

2022-01-08
on
2022-01-08
6


SELECT 
    Z.*, 
    Seq = Z.[Days],
    diff = 
        DATEDIFF(DAY, '2022-01-01', Z.[Days]) - 
            ROW_NUMBER() OVER (
                ORDER BY Z.[Days]) 
FROM dbo.z_test_duration AS Z
WHERE Z.[Status] = 'on';


Days
Status
Seq
diff


2022-01-01
on
2022-01-01
-1

2022-01-02
on
2022-01-02
-1

2022-01-03
on
2022-01-03
-1

2022-01-05
on
2022-01-05
0

2022-01-07
on
2022-01-07
1

2022-01-08
on
2022-01-08
1


SELECT
    ON_DATE = MIN(G.Seq), 
    OF_DATE = DATEADD(DAY, 1, MAX(G.Seq)),
    COUNT_OF_ACTIVE_DAYS = 1 + DATEDIFF(DAY, MIN(G.Seq), MAX(G.Seq))
FROM 
(
    SELECT 
        Z.*, 
        Seq = Z.[Days],
        grp = 
            DATEDIFF(DAY, '2022-01-01', Z.[Days]) - 
                ROW_NUMBER() OVER (
                    ORDER BY Z.[Days]) 
    FROM dbo.z_test_duration AS Z
    WHERE Z.[Status] = 'on'
) AS G
GROUP BY G.grp;


ON_DATE
OF_DATE
COUNT_OF_ACTIVE_DAYS


2022-01-01
2022-01-04
3

2022-01-05
2022-01-06
1

2022-01-07
2022-01-09
2

Days	Status	Seq	rn
2022-01-01	on	2022-01-01	1
2022-01-02	on	2022-01-02	2
2022-01-03	on	2022-01-03	3
2022-01-05	on	2022-01-05	4
2022-01-07	on	2022-01-07	5
2022-01-08	on	2022-01-08	6

ON_DATE	OF_DATE	COUNT_OF_ACTIVE_DAYS
2022-01-01	2022-01-04	3
2022-01-05	2022-01-06	1
2022-01-07	2022-01-09	2