Oracle 11g Release 2

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

alter session set nls_timestamp_format='DD/MM/YYYY HH24:MI:SS';
create table mytable (
  name varchar(20),
  operation varchar(20),
  timestamp timestamp
);
insert into mytable
select 'A' as name,       'START' as operation,      '18/01/2024 8:24' as timestamp from dual union all
select 'A',       'START',      '18/01/2024 8:27' from dual union all
select 'A',       'START',      '18/01/2024 8:30' from dual union all
select 'A',       'END',        '18/01/2024 8:34' from dual union all
select 'B',       'START',      '18/01/2024 9:28' from dual union all
select 'B',       'END',        '18/01/2024 9:32'  from dual union all
select 'B',       'START',      '18/01/2024 9:37'  from dual union all
select 'B',       'END',        '18/01/2024 9:39'  from dual union all
select 'C',       'START',      '18/01/2024 10:14' from dual union all
select 'C',       'END',        '18/01/2024 10:18' from dual;
10 rows affected

WITH cte AS (
  SELECT name, operation, timestamp,
       ROW_NUMBER() OVER (PARTITION BY name ORDER BY timestamp DESC) 
       - SUM(CASE WHEN operation = 'START' THEN 1 ELSE 0 END) OVER (PARTITION BY name ORDER BY timestamp DESC) as grp
  FROM mytable
)
SELECT *
FROM cte



NAME
OPERATION
TIMESTAMP
GRP


A
END
18/01/2024 08:34:00
1

A
START
18/01/2024 08:30:00
1

A
START
18/01/2024 08:27:00
1

A
START
18/01/2024 08:24:00
1

B
END
18/01/2024 09:39:00
1

B
START
18/01/2024 09:37:00
1

B
END
18/01/2024 09:32:00
2

B
START
18/01/2024 09:28:00
2

C
END
18/01/2024 10:18:00
1

C
START
18/01/2024 10:14:00
1


WITH cte AS (
  SELECT name, operation, timestamp,
       ROW_NUMBER() OVER (PARTITION BY name ORDER BY timestamp DESC) 
       - SUM(CASE WHEN operation = 'START' THEN 1 ELSE 0 END) OVER (PARTITION BY name ORDER BY timestamp DESC) as grp
  FROM mytable
)
SELECT name, 
       MIN(CASE WHEN OPERATION = 'START' THEN 'START' END) AS START_EVENT,
       MIN(CASE WHEN OPERATION = 'START' THEN TIMESTAMP END) AS START_TIME,
       MIN(CASE WHEN OPERATION = 'END' THEN 'END' END) AS END_EVENT,
       MIN(CASE WHEN OPERATION = 'END' THEN TIMESTAMP END) AS END_TIME,
       MIN(CASE WHEN OPERATION = 'END' THEN TIMESTAMP END) 
       - MIN(CASE WHEN OPERATION = 'START' THEN TIMESTAMP END) AS duration
FROM cte
GROUP BY name, grp
ORDER BY name, START_EVENT


NAME
START_EVENT
START_TIME
END_EVENT
END_TIME
DURATION


A
START
18/01/2024 08:24:00
END
18/01/2024 08:34:00
+000000000 00:10:00.000000

B
START
18/01/2024 09:28:00
END
18/01/2024 09:32:00
+000000000 00:04:00.000000

B
START
18/01/2024 09:37:00
END
18/01/2024 09:39:00
+000000000 00:02:00.000000

C
START
18/01/2024 10:14:00
END
18/01/2024 10:18:00
+000000000 00:04:00.000000

NAME	OPERATION	TIMESTAMP	GRP
A	END	18/01/2024 08:34:00	1
A	START	18/01/2024 08:30:00	1
A	START	18/01/2024 08:27:00	1
A	START	18/01/2024 08:24:00	1
B	END	18/01/2024 09:39:00	1
B	START	18/01/2024 09:37:00	1
B	END	18/01/2024 09:32:00	2
B	START	18/01/2024 09:28:00	2
C	END	18/01/2024 10:18:00	1
C	START	18/01/2024 10:14:00	1