How to do a query on Oracle SQL to get time intervals, grouping by specific fields

I love a good challenge, but this one has been breaking my head for too long. 🙂

I’m trying to build a query to get dates intervals, grouping the information by one field.

Let me try to explain it in a simple way. We have this table:

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

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

Any suggestion on how to build a query to do this?

THANK YOU!

Answer

From Oracle 12, you can use MATCH_RECOGNIZE:

SELECT *
FROM   table_name
MATCH_RECOGNIZE (
  PARTITION BY id
  ORDER     BY start_date, end_date
  MEASURES
    FIRST( name )       AS name,
    FIRST( ranking )    AS ranking,
    FIRST( start_date ) AS start_date,
    LAST( end_Date )    AS end_Date
  PATTERN ( same_rank+ )
  DEFINE same_rank AS FIRST( ranking ) = ranking
)

Which, for the sample data:

CREATE TABLE table_name ( id, name, ranking, start_date, end_date ) AS
SELECT 1001, 'Jones', 'Lieutenant', DATE '2000-03-20', DATE '2002-08-15' FROM DUAL UNION ALL
SELECT 1001, 'Jones', 'Lieutenant', DATE '2002-08-16', DATE '2003-03-18' FROM DUAL UNION ALL
SELECT 1001, 'Jones', 'Lieutenant', DATE '2003-03-19', DATE '2004-06-01' FROM DUAL UNION ALL
SELECT 1001, 'Jones', 'Lieutenant', DATE '2004-06-02', DATE '2004-10-01' FROM DUAL UNION ALL
SELECT 1001, 'Jones', 'Captain',    DATE '2004-10-02', DATE '2005-04-20' FROM DUAL UNION ALL
SELECT 1001, 'Jones', 'Captain',    DATE '2005-04-21', DATE '2007-02-20' FROM DUAL UNION ALL
SELECT 1001, 'Jones', 'Major',      DATE '2007-02-21', DATE '2008-10-22' FROM DUAL UNION ALL
SELECT 1001, 'Jones', 'Major',      DATE '2008-10-23', DATE '2010-01-26' FROM DUAL UNION ALL
SELECT 1001, 'Jones', 'Captain',    DATE '2010-01-27', DATE '2013-11-25' FROM DUAL UNION ALL
SELECT 1001, 'Jones', 'Captain',    DATE '2013-11-26', DATE '2014-05-11' FROM DUAL UNION ALL
SELECT 1001, 'Jones', 'Major',      DATE '2014-05-12', DATE '2016-04-22' FROM DUAL UNION ALL
SELECT 1001, 'Jones', 'General',    DATE '2016-04-23', DATE '2020-10-10' FROM DUAL UNION ALL
SELECT 1001, 'Jones', 'General',    DATE '2020-10-11', DATE '2020-11-30' FROM DUAL;

Outputs:

  ID | NAME  | RANKING    | START_DATE          | END_DATE           
---: | :---- | :--------- | :------------------ | :------------------
1001 | Jones | Lieutenant | 2000-03-20 00:00:00 | 2004-10-01 00:00:00
1001 | Jones | Captain    | 2004-10-02 00:00:00 | 2007-02-20 00:00:00
1001 | Jones | Major      | 2007-02-21 00:00:00 | 2010-01-26 00:00:00
1001 | Jones | Captain    | 2010-01-27 00:00:00 | 2014-05-11 00:00:00
1001 | Jones | Major      | 2014-05-12 00:00:00 | 2016-04-22 00:00:00
1001 | Jones | General    | 2016-04-23 00:00:00 | 2020-11-30 00:00:00

db<>fiddle here