SQL Server 2022

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

CREATE TABLE t(
   Date      DATE  NOT NULL
  ,Name      VARCHAR(6) NOT NULL
  ,Indicator INT 
);
INSERT INTO t(Date,Name,Indicator) VALUES ('01/12/2022','Test1',1);
INSERT INTO t(Date,Name,Indicator) VALUES ('02/12/2022','Test2',NULL);
INSERT INTO t(Date,Name,Indicator) VALUES ('03/12/2022','Test3',NULL);
INSERT INTO t(Date,Name,Indicator) VALUES ('04/12/2022','Test4',NULL);
INSERT INTO t(Date,Name,Indicator) VALUES ('05/12/2022','Test5',1);
INSERT INTO t(Date,Name,Indicator) VALUES ('06/12/2022','Test6',NULL);
INSERT INTO t(Date,Name,Indicator) VALUES ('07/12/2022','Test7',1);
INSERT INTO t(Date,Name,Indicator) VALUES ('08/12/2022','Test8',NULL);
8 rows affected

select x.*, row_number() over(partition by g order by date) as instance
from (select t.*, sum(indicator) over(order by date) as g from t) x


Date
Name
Indicator
g
instance


2022-01-12
Test1
1
1
1

2022-02-12
Test2
null
1
2

2022-03-12
Test3
null
1
3

2022-04-12
Test4
null
1
4

2022-05-12
Test5
1
2
1

2022-06-12
Test6
null
2
2

2022-07-12
Test7
1
3
1

2022-08-12
Test8
null
3
2


Warning: Null value is eliminated by an aggregate or other SET operation.

Date	Name	Indicator	g	instance
2022-01-12	Test1	1	1	1
2022-02-12	Test2	null	1	2
2022-03-12	Test3	null	1	3
2022-04-12	Test4	null	1	4
2022-05-12	Test5	1	2	1
2022-06-12	Test6	null	2	2
2022-07-12	Test7	1	3	1
2022-08-12	Test8	null	3	2