Skip to Main Content

Breadcrumb

Dev Live Dev Intro

We are celebrating Developers at AskTOM. We welcome Developers of all levels of experience to join us at our FREE Developer Live events coming in August and September. Just click on the left to register today! If you are brand new to Database Technology, then we also have got you covered. Just click on the right for your comprehensive FREE training program to kick start your Oracle Database Development journey!

Question and Answer

Chris Saxon

Thanks for the question, Mohammed .

Asked: August 11, 2020 - 5:52 am UTC

Answered by: Chris Saxon - Last updated: August 11, 2020 - 1:24 pm UTC

Category: PL/SQL - Version: 11G

Viewed 100+ times

You Asked

i want to make rankning of trucks exit based on break more than 1 hour

like eg below


TRUCK EXIT
T1 10:00 PM
T2 10:05 PM
T3 12:00 PM
T4 12:05 PM
T5 12:10 PM
T6 12:20 PM

Result should be like...... leaving break more than 1 hours gaps

10:00 10:05 1
12:00 12:20 2




with LiveSQL Test Case:

and we said...

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 

and you rated our response

  (1 rating)

Reviews

Wow that works like charm

August 11, 2020 - 12:04 pm UTC

Reviewer: A reader

awesome query chris thanks alooooooooooooot
Chris Saxon

Followup  

August 11, 2020 - 1:24 pm UTC

You're welcoooooommmmmeeee

More to Explore

Analytics

Analytic SQL got you confused? Check out Connor McDonald's complete video course.