Skip to Main Content
  • Questions
  • How can I get min_date and max_date, grouping by some field

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Pedro.

Asked: November 30, 2020 - 12:06 pm UTC

Last updated: November 30, 2020 - 4:52 pm UTC

Version: Oracle 12

Viewed 100+ times

You Asked

Hi.
I'm trying to build a query to get dates intervals.
Let me explain it in a simple way.
We have a table with this fields:

Soldier_ID  |  Soldier_Name  |  Location     |  Ranking     |  Start_Date  |  End_Date
1001           Jones            Texas           Lieutenant     2000/03/20     2002/08/15
1001           Jones            Afghanistan     Lieutenant     2002/08/16     2003/03/18
1001           Jones            Michigan        Lieutenant     2003/03/19     2004/06/01
1001           Jones            NY              Lieutenant     2004/06/02     2004/10/01
1001           Jones            NY              Captain        2004/10/02     2005/04/20
1001           Jones            Berlin          Captain        2005/04/21     2007/02/20
1001           Jones            Michigan        Major          2007/02/21     2008/10/22
1001           Jones            Ohio            Major          2008/10/23     2010/01/26
1001           Jones            Ohio            Captain        2010/01/27     2013/11/26
1001           Jones            Texas           Captain        2013/11/26     2014/05/11
1001           Jones            Texas           Major          2014/05/12     2016/04/22
1001           Jones            Texas           General        2016/04/23     2020/10/10
1001           Jones            Washington      General        2020/10/11     2020/11/30


I need to get the time intervals that soldier spent on each ranking, so the end result I need to get should be something like this:

Soldier_ID  |  Soldier_Name  |  Ranking     |  Start_Date  |  End_Date
1001           Jones            Lieutenant     2000/03/20     2004/10/01
1001           Jones            Captain        2004/10/02     2007/02/20
1001           Jones            Major          2007/02/21     2010/01/26
1001           Jones            Captain        2010/01/27     2014/05/11
1001           Jones            Major          2014/05/12     2016/04/22
1001           Jones            General        2016/04/23     2020/11/30


As you can see the soldier is promoted/demoted along the time.

Any suggestion on how to do this?

and we said...

You can do this with pattern matching.

The pattern you're looking for is:

Any row followed by zero or more rows which have the same rank as the previous

Which you can do with this pattern and variable:

    pattern ( init same_rank* )
    define
      same_rank as rk = prev ( rk )


All together this looks like:

with rws as (
select 1001 id, 'Jones' nm, 'Texas' loc, 'Lieutenant' rk, to_date ( '2000/03/20', 'yyyy/mm/dd' ) st_dt, to_date ( '2002/08/15', 'yyyy/mm/dd' ) en_dt from dual union all
select 1001 id, 'Jones' nm, 'Afghanistan' loc, 'Lieutenant' rk, to_date ( '2002/08/16', 'yyyy/mm/dd' ) st_dt, to_date ( '2003/03/18', 'yyyy/mm/dd' ) en_dt from dual union all
select 1001 id, 'Jones' nm, 'Michigan' loc, 'Lieutenant' rk, to_date ( '2003/03/19', 'yyyy/mm/dd' ) st_dt, to_date ( '2004/06/01', 'yyyy/mm/dd' ) en_dt from dual union all
select 1001 id, 'Jones' nm, 'NY' loc, 'Lieutenant' rk, to_date ( '2004/06/02', 'yyyy/mm/dd' ) st_dt, to_date ( '2004/10/01', 'yyyy/mm/dd' ) en_dt from dual union all
select 1001 id, 'Jones' nm, 'NY' loc, 'Captain' rk, to_date ( '2004/10/02', 'yyyy/mm/dd' ) st_dt, to_date ( '2005/04/20', 'yyyy/mm/dd' ) en_dt from dual union all
select 1001 id, 'Jones' nm, 'Berlin' loc, 'Captain' rk, to_date ( '2005/04/21', 'yyyy/mm/dd' ) st_dt, to_date ( '2007/02/20', 'yyyy/mm/dd' ) en_dt from dual union all
select 1001 id, 'Jones' nm, 'Michigan' loc, 'Major' rk, to_date ( '2007/02/21', 'yyyy/mm/dd' ) st_dt, to_date ( '2008/10/22', 'yyyy/mm/dd' ) en_dt from dual union all
select 1001 id, 'Jones' nm, 'Ohio' loc, 'Major' rk, to_date ( '2008/10/23', 'yyyy/mm/dd' ) st_dt, to_date ( '2010/01/26', 'yyyy/mm/dd' ) en_dt from dual union all
select 1001 id, 'Jones' nm, 'Ohio' loc, 'Captain' rk, to_date ( '2010/01/27', 'yyyy/mm/dd' ) st_dt, to_date ( '2013/11/26', 'yyyy/mm/dd' ) en_dt from dual union all
select 1001 id, 'Jones' nm, 'Texas' loc, 'Captain' rk, to_date ( '2013/11/26', 'yyyy/mm/dd' ) st_dt, to_date ( '2014/05/11', 'yyyy/mm/dd' ) en_dt from dual union all
select 1001 id, 'Jones' nm, 'Texas' loc, 'Major' rk, to_date ( '2014/05/12', 'yyyy/mm/dd' ) st_dt, to_date ( '2016/04/22', 'yyyy/mm/dd' ) en_dt from dual union all
select 1001 id, 'Jones' nm, 'Texas' loc, 'General' rk, to_date ( '2016/04/23', 'yyyy/mm/dd' ) st_dt, to_date ( '2020/10/10', 'yyyy/mm/dd' ) en_dt from dual union all
select 1001 id, 'Jones' nm, 'Washington' loc, 'General' rk, to_date ( '2020/10/11', 'yyyy/mm/dd' ) st_dt, to_date ( '2020/11/30', 'yyyy/mm/dd' ) en_dt from dual 
)
  select *
  from   rws match_recognize (
    order by st_dt
    measures 
      first ( st_dt ) st_dt,
      last ( en_dt ) en_dt,
      last ( id ) id,
      last ( nm ) nm,
      last ( rk ) rk
    pattern ( init same_rank* )
    define
      same_rank as rk = prev ( rk )
  );
  
ST_DT                   EN_DT                   ID      NM       RK           
20-MAR-2000 00:00:00    01-OCT-2004 00:00:00       1001 Jones    Lieutenant    
02-OCT-2004 00:00:00    20-FEB-2007 00:00:00       1001 Jones    Captain       
21-FEB-2007 00:00:00    26-JAN-2010 00:00:00       1001 Jones    Major         
27-JAN-2010 00:00:00    11-MAY-2014 00:00:00       1001 Jones    Captain       
12-MAY-2014 00:00:00    22-APR-2016 00:00:00       1001 Jones    Major         
23-APR-2016 00:00:00    30-NOV-2020 00:00:00       1001 Jones    General    


For more on how to use pattern matching, see:

https://livesql.oracle.com/apex/livesql/file/content_KPC7JMI7AXP22YR76GWPFITCT.html
https://speakerdeck.com/chrissaxon/how-to-find-patterns-in-your-data-with-sql

More to Explore

Analytics

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