I want to look at the lead type and if that type is the same for that row then merge in those dates to fit within one row.
I have the below table:
id start_dt end_dt type 1 1/1/19 2/21/19 cross 1 2/22/19 6/5/19 cross 1 6/6/19 8/31/19 cross 1 9/1/19 10/3/19 AAAA 1 10/4/19 10/4/19 cross 1 10/5/19 10/6/19 AAAA 1 10/7/19 10/10/19 AAAA 1 10/11/19 12/31/99 cross
Expected Results:
id start_dt end_dt type 1 1/1/19 8/31/19 cross 1 9/1/19 10/3/19 AAAA 1 10/4/19 10/4/19 cross 1 10/5/19 10/10/19 AAAA 1 10/11/19 12/31/99 cross
How can I get my output to look like the expected results?
I have tested withlead lag rank and case expression but nothing worthy of adding here. Am I on the right path?
Advertisement
Answer
This is a gaps-and-islands problem. One option for solving it through contribution of row_number() analytical function :
select min(start_dt) as startdate, max(end_dt) as enddate, type
from
(
with t(id, start_dt, end_dt,type) as
(
select 1, date'2019-01-01', date'2019-02-21', 'cross' from dual union all
select 1, date'2019-02-22', date'2019-06-05', 'cross' from dual union all
select 1, date'2019-06-06', date'2019-08-31', 'cross' from dual union all
select 1, date'2019-09-01', date'2019-10-03', 'AAAA' from dual union all
select 1, date'2019-09-04', date'2019-10-04', 'cross' from dual union all
select 1, date'2019-10-05', date'2019-10-06', 'AAAA' from dual union all
select 1, date'2019-10-07', date'2019-10-10', 'AAAA' from dual union all
select 1, date'2019-10-11', date'2019-12-31', 'cross' from dual
)
select type,
row_number() over (partition by id, type order by end_dt) as rn1,
row_number() over (partition by id order by end_dt) as rn2,
start_dt, end_dt
from t
) tt
group by type, rn1 - rn2
order by enddate;
STARTDATE ENDDATE TYPE
--------- --------- -----
01-JAN-19 31-AUG-19 cross
01-SEP-19 03-OCT-19 AAAA
04-SEP-19 04-OCT-19 cross
05-OCT-19 10-OCT-19 AAAA
11-OCT-19 31-DEC-19 cross