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?
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