This is my test case:
create table example
( id number,
p_col varchar2(2),
o_col varchar2(2),
g_col varchar2(2),
s_date date,
v_num number
);
insert into example values ( 1, 'A','S','X1', to_date('21.05.2026 10:00','dd.mm.yyyy hh24:mi'), 12.3);
insert into example values ( 2, 'A','T','X1', to_date('21.05.2026 10:00','dd.mm.yyyy hh24:mi'), 4.6);
insert into example values ( 3, 'B','S','Y1', to_date('21.05.2026 10:00','dd.mm.yyyy hh24:mi'), 3.8);
insert into example values ( 4, 'A',null,null, to_date('21.05.2026 10:35','dd.mm.yyyy hh24:mi'), 0.0);
insert into example values ( 5, 'A','S','X1', to_date('21.05.2026 10:51','dd.mm.yyyy hh24:mi'), 12.3);
insert into example values ( 6, 'A','T','X1', to_date('21.05.2026 10:51','dd.mm.yyyy hh24:mi'), 4.6);
insert into example values ( 7, 'A','U','X2', to_date('21.05.2026 11:17','dd.mm.yyyy hh24:mi'), 12.3);
insert into example values ( 8, 'A','T','X1', to_date('21.05.2026 11:39','dd.mm.yyyy hh24:mi'), 14.7);
insert into example values ( 9, 'A','S','X1', to_date('21.05.2026 11:39','dd.mm.yyyy hh24:mi'), 10.1);
insert into example values (10, 'B','T','Y1', to_date('21.05.2026 11:45','dd.mm.yyyy hh24:mi'), 9.8);
insert into example values (11, 'A','T','X1', to_date('21.05.2026 11:55','dd.mm.yyyy hh24:mi'), 4.7);
insert into example values (12, 'A','S','X1', to_date('21.05.2026 11:55','dd.mm.yyyy hh24:mi'), 12.2);
insert into example values (13, 'B',null,null, to_date('21.05.2026 12:01','dd.mm.yyyy hh24:mi'), 0.0);
insert into example values (14, 'A','Q','X3', to_date('21.05.2026 12:01','dd.mm.yyyy hh24:mi'), 1.2);
insert into example values (15, 'A','S','X3', to_date('21.05.2026 12:01','dd.mm.yyyy hh24:mi'), 10.1);
insert into example values (16, 'A','T','X3', to_date('21.05.2026 12:01','dd.mm.yyyy hh24:mi'), 9.9);
insert into example values (17, 'B','S','Y1', to_date('21.05.2026 12:16','dd.mm.yyyy hh24:mi'), 7.8);
insert into example values (18, 'A','U','X2', to_date('21.05.2026 12:20','dd.mm.yyyy hh24:mi'), 12.0);
insert into example values (19, 'A',null,null, to_date('21.05.2026 12:34','dd.mm.yyyy hh24:mi'), 0.0);
So there are two different processes (A,B) where several operations (defined by o_col and g_col) can run in parallel. Here g_col has the same value in one group of parallel operations while the values of o_col must all be different at a certain time. I would like to see the duration of each operation until the next group of operations starts on a process.
I get the desired result with the following query but I just wonder if there is a more direct way in a single select using the right analytic functions.
with n as
(select
p_col,
s_date,
lead(s_date) over (partition by p_col order by s_date) e_date
from example
group by
p_col,
s_date
)
select
e.id,
e.p_col,
e.o_col,
e.g_col,
e.s_date,
round((n.e_date - e.s_date)*24*60) minutes,
e.v_num
from example e
join n on n.p_col = e.p_col and n.s_date = e.s_date
order by e.p_col, e.s_date, e.id;