SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE mytable(
   Event1 INT  NOT NULL 
  ,Event2 INT  NOT NULL
  ,Status VARCHAR(8) NOT NULL
);
INSERT INTO mytable(Event1,Event2,Status) VALUES (1,1,'Start');
INSERT INTO mytable(Event1,Event2,Status) VALUES (1,2,'Middle');
INSERT INTO mytable(Event1,Event2,Status) VALUES (1,3,'Middle');
INSERT INTO mytable(Event1,Event2,Status) VALUES (1,4,'End');
INSERT INTO mytable(Event1,Event2,Status) VALUES (1,5,'Start');
INSERT INTO mytable(Event1,Event2,Status) VALUES (1,6,'Middle');
INSERT INTO mytable(Event1,Event2,Status) VALUES (1,7,'Middle');
INSERT INTO mytable(Event1,Event2,Status) VALUES (2,10,'Start');
INSERT INTO mytable(Event1,Event2,Status) VALUES (2,11,'Middle');
INSERT INTO mytable(Event1,Event2,Status) VALUES (2,12,'End');
INSERT INTO mytable(Event1,Event2,Status) VALUES (2,13,'Start');
INSERT INTO mytable(Event1,Event2,Status) VALUES (2,14,'End');
INSERT INTO mytable(Event1,Event2,Status) VALUES (2,15,'Start');
13 rows affected

	select
		t.*,
		sum(case when Status = 'Start' then 1 else 0 end) 
			over(partition by Event1 order by Event2) grp
	from mytable t


Event1
Event2
Status
grp


1
1
Start
1

1
2
Middle
1

1
3
Middle
1

1
4
End
1

1
5
Start
2

1
6
Middle
2

1
7
Middle
2

2
10
Start
1

2
11
Middle
1

2
12
End
1

2
13
Start
2

2
14
End
2

2
15
Start
3


	select 
		t.*,
		row_number() over(partition by Event1, grp order by Event2 desc) rn
	from (
		select
			t.*,
			sum(case when Status = 'Start' then 1 else 0 end) 
				over(partition by Event1 order by Event2) grp
		from mytable t
	) t
	order by event1, event2


Event1
Event2
Status
grp
rn


1
1
Start
1
4

1
2
Middle
1
3

1
3
Middle
1
2

1
4
End
1
1

1
5
Start
2
3

1
6
Middle
2
2

1
7
Middle
2
1

2
10
Start
1
3

2
11
Middle
1
2

2
12
End
1
1

2
13
Start
2
2

2
14
End
2
1

2
15
Start
3
1


select 
    Event1,
    min(Event2) StartEvent2,
    max(case when rn = 1 and status = 'End' then Event2 end) EndEvent2
from (
    select 
        t.*,
        row_number() over(partition by Event1, grp order by Event2 desc) rn
    from (
        select
            t.*,
            sum(case when Status = 'Start' then 1 else 0 end) 
                over(partition by Event1 order by Event2) grp
        from mytable t
    ) t
) t
group by Event1, grp
order by Event1, grp


Event1
StartEvent2
EndEvent2


1
1
4

1
5
null

2
10
12

2
13
14

2
15
null


Warning: Null value is eliminated by an aggregate or other SET operation.
Event1	Event2	Status	grp
1	1	Start	1
1	2	Middle	1
1	3	Middle	1
1	4	End	1
1	5	Start	2
1	6	Middle	2
1	7	Middle	2
2	10	Start	1
2	11	Middle	1
2	12	End	1
2	13	Start	2
2	14	End	2
2	15	Start	3