I can't get your Live SQL link to work - could you check this please?
Also it's not clear what a break of more than 1 hour means:
* 1 hour since the last exit time?
* 1 hour since the first exit time of the group?
Either way, from 12c this is simple - use pattern matching!
I'm assuming you mean 1 hour since the last exit. In which case you want to group together all the rows where:
The current exit time < previous exit time + 1 hour
We can define this as the pattern variable:
within_hour as exit_time < prev ( exit_time ) + 1/24
If you want the time since the first exit in the group, change prev -> first.
There can be any number of rows in this group. So you want to search for any row followed by zero or more within_hour rows:
pattern ( init within_hour* )
Giving:
create table t (
truck varchar2(2),
exit_time date
);
insert into t values ( 'T1', to_date ( '2020-08-11 10:00 AM', 'yyyy-mm-dd hh:mi am' ) );
insert into t values ( 'T2', to_date ( '2020-08-11 10:05 AM', 'yyyy-mm-dd hh:mi am' ) );
insert into t values ( 'T3', to_date ( '2020-08-11 12:00 PM', 'yyyy-mm-dd hh:mi am' ) );
insert into t values ( 'T4', to_date ( '2020-08-11 12:05 PM', 'yyyy-mm-dd hh:mi am' ) );
insert into t values ( 'T5', to_date ( '2020-08-11 12:10 PM', 'yyyy-mm-dd hh:mi am' ) );
insert into t values ( 'T6', to_date ( '2020-08-11 12:20 PM', 'yyyy-mm-dd hh:mi am' ) );
commit;
select * from t
match_recognize (
order by exit_time
measures
first ( exit_time ) as start_time,
last ( exit_time ) as end_time,
match_number() as grp
pattern ( init within_hour* )
define
within_hour as exit_time < prev ( exit_time ) + 1/24
);
START_TIME END_TIME GRP
11-AUG-2020 10:00:00 11-AUG-2020 10:05:00 1
11-AUG-2020 12:00:00 11-AUG-2020 12:20:00 2
I see you're on 11g, so this won't work :( There are various other ways you can tackle this, such as using recursive with:
with rws as (
select t.*,
row_number () over (
order by exit_time
) rn
from t
), grps ( exit_time, start_time, end_time, rn, grp ) as (
select exit_time, exit_time, exit_time, rn, 1
from rws
where rn = 1
union all
select r.exit_time,
case
when r.exit_time > g.end_time + 1/24 then r.exit_time
else g.start_time
end,
r.exit_time, r.rn,
case
when r.exit_time > g.end_time + 1/24 then grp + 1
else grp
end
from grps g
join rws r
on g.rn + 1 = r.rn
)
select start_time, max ( end_time ), grp
from grps
group by grp, start_time
order by grp;
START_TIME MAX(END_TIME) GRP
11-AUG-2020 10:00:00 11-AUG-2020 10:05:00 1
11-AUG-2020 12:00:00 11-AUG-2020 12:20:00 2