Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select * from V$VERSION;


BANNER
BANNER_FULL
BANNER_LEGACY
CON_ID


Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production
Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production
Version 18.4.0.0.0
Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production
0


create table atest (
    TR_ID NUMBER(9),
    TR_START DATE,
    TR_END DATE,
    TR_FROMSTART VARCHAR2(255),
    TR_FROMEND VARCHAR2(255) )
alter session set nls_date_format='dd.mm.yyyy hh24:mi'
insert into atest 
with atest (TR_ID, TR_START, TR_END, TR_FROMSTART, TR_FROMEND) as ( 
    select 1, '01.06.2020 04:50', '01.06.2020 07:50', 'MSK', 'KZN' from dual union all  
    select 1, '01.06.2020 09:35', '01.06.2020 13:00', 'KZN', 'ANP' from dual union all  
    select 2, '03.06.2020 15:35', '03.06.2020 18:30', 'MSK', 'SPB' from dual union all  
    select 2, '05.06.2020 05:15', '05.06.2020 10:30', 'SPB', 'MSK' from dual union all  
    select 3, '05.06.2020 12:05', '05.06.2020 12:05', 'MSK', 'NSB' from dual union all  
    select 3, '08.06.2020 17:05', '09.06.2020 01:50', 'NSB', 'MSK' from dual union all  
    select 1, '10.06.2020 12:30', '10.06.2020 16:00', 'ANP', 'KZN' from dual union all  
    select 1, '12.06.2020 17:20', '13.06.2020 05:35', 'KZN', 'MSK' from dual union all  
    select 2, '15.06.2020 05:15', '15.06.2020 10:30', 'MSK', 'ANP' from dual union all  
    select 2, '15.06.2020 12:05', '15.06.2020 17:50', 'ANP', 'KZN' from dual
)
select * from atest
10 rows affected

select * 
from atest t1
outer apply (
    select 
        max (tr_start) keep (dense_rank last order by t2.tr_start asc) last_start,
        max (tr_end)   keep (dense_rank last order by t2.tr_end   asc) last_end
    from atest t2
    where t2.tr_id  = t1.tr_Id 
    and   t2.tr_fromend = t1.tr_fromstart  
    and   t2.tr_end < t1.tr_start
)
order by t1.tr_start


TR_ID
TR_START
TR_END
TR_FROMSTART
TR_FROMEND
LAST_START
LAST_END


1
01.06.2020 04:50
01.06.2020 07:50
MSK
KZN
null
null

1
01.06.2020 09:35
01.06.2020 13:00
KZN
ANP
01.06.2020 04:50
01.06.2020 07:50

2
03.06.2020 15:35
03.06.2020 18:30
MSK
SPB
null
null

2
05.06.2020 05:15
05.06.2020 10:30
SPB
MSK
03.06.2020 15:35
03.06.2020 18:30

3
05.06.2020 12:05
05.06.2020 12:05
MSK
NSB
null
null

3
08.06.2020 17:05
09.06.2020 01:50
NSB
MSK
05.06.2020 12:05
05.06.2020 12:05

1
10.06.2020 12:30
10.06.2020 16:00
ANP
KZN
01.06.2020 09:35
01.06.2020 13:00

1
12.06.2020 17:20
13.06.2020 05:35
KZN
MSK
10.06.2020 12:30
10.06.2020 16:00

2
15.06.2020 05:15
15.06.2020 10:30
MSK
ANP
05.06.2020 05:15
05.06.2020 10:30

2
15.06.2020 12:05
15.06.2020 17:50
ANP
KZN
15.06.2020 05:15
15.06.2020 10:30


with t as (
    select tr.*, 
        dense_rank () over (partition by tr.tr_id order by tr.tr_start desc) rnk
    from atest tr
)
select 
    t1.tr_id, t1.tr_start, t1.tr_end, t1.tr_fromstart, t1.tr_fromend, 
    t2.tr_start last_start, t2.tr_end last_end
from t t1
left join t t2 on (
    t2.tr_id = t1.tr_id and 
    t2.tr_fromend = t1.tr_fromstart 
    and t2.rnk = (t1.rnk+1))
order by t1.tr_start


TR_ID
TR_START
TR_END
TR_FROMSTART
TR_FROMEND
LAST_START
LAST_END


1
01.06.2020 04:50
01.06.2020 07:50
MSK
KZN
null
null

1
01.06.2020 09:35
01.06.2020 13:00
KZN
ANP
01.06.2020 04:50
01.06.2020 07:50

2
03.06.2020 15:35
03.06.2020 18:30
MSK
SPB
null
null

2
05.06.2020 05:15
05.06.2020 10:30
SPB
MSK
03.06.2020 15:35
03.06.2020 18:30

3
05.06.2020 12:05
05.06.2020 12:05
MSK
NSB
null
null

3
08.06.2020 17:05
09.06.2020 01:50
NSB
MSK
05.06.2020 12:05
05.06.2020 12:05

1
10.06.2020 12:30
10.06.2020 16:00
ANP
KZN
01.06.2020 09:35
01.06.2020 13:00

1
12.06.2020 17:20
13.06.2020 05:35
KZN
MSK
10.06.2020 12:30
10.06.2020 16:00

2
15.06.2020 05:15
15.06.2020 10:30
MSK
ANP
05.06.2020 05:15
05.06.2020 10:30

2
15.06.2020 12:05
15.06.2020 17:50
ANP
KZN
15.06.2020 05:15
15.06.2020 10:30

TR_ID	TR_START	TR_END	TR_FROMSTART	TR_FROMEND	LAST_START	LAST_END
1	01.06.2020 04:50	01.06.2020 07:50	MSK	KZN	null	null
1	01.06.2020 09:35	01.06.2020 13:00	KZN	ANP	01.06.2020 04:50	01.06.2020 07:50
2	03.06.2020 15:35	03.06.2020 18:30	MSK	SPB	null	null
2	05.06.2020 05:15	05.06.2020 10:30	SPB	MSK	03.06.2020 15:35	03.06.2020 18:30
3	05.06.2020 12:05	05.06.2020 12:05	MSK	NSB	null	null
3	08.06.2020 17:05	09.06.2020 01:50	NSB	MSK	05.06.2020 12:05	05.06.2020 12:05
1	10.06.2020 12:30	10.06.2020 16:00	ANP	KZN	01.06.2020 09:35	01.06.2020 13:00
1	12.06.2020 17:20	13.06.2020 05:35	KZN	MSK	10.06.2020 12:30	10.06.2020 16:00
2	15.06.2020 05:15	15.06.2020 10:30	MSK	ANP	05.06.2020 05:15	05.06.2020 10:30
2	15.06.2020 12:05	15.06.2020 17:50	ANP	KZN	15.06.2020 05:15	15.06.2020 10:30