Oracle 11g Release 2

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

create table asset_data_historical(assetid integer, data_received_on date);
insert into asset_data_historical(assetid,data_received_on) 
values(17085,to_date('201708232250','YYYYMMDDHH24MI'));
1 rows affected

insert into asset_data_historical(assetid,data_received_on) 
values(17085,to_date('201708252054','YYYYMMDDHH24MI'));
1 rows affected

insert into asset_data_historical(assetid,data_received_on) 
values(17085,to_date('201708252108','YYYYMMDDHH24MI'));
1 rows affected

insert into asset_data_historical(assetid,data_received_on) 
values(17085,to_date('201708311849','YYYYMMDDHH24MI'));
1 rows affected

insert into asset_data_historical(assetid,data_received_on) 
values(17085,to_date('201709291851','YYYYMMDDHH24MI'));
1 rows affected

create table customer_contacted_logs(
  ticketid integer
, assetid integer
, last_contacted_on date
);
insert into customer_contacted_logs(ticketid,assetid,last_contacted_on)
values(1785487,17085,to_date('201709282157','YYYYMMDDHH24MI'));
1 rows affected

insert into customer_contacted_logs(ticketid,assetid,last_contacted_on)
values(1785491,17085,to_date('201710161851','YYYYMMDDHH24MI'));
1 rows affected

alter session set nls_date_format = 'DD-MON-YYYY HH24:MI';
select * from asset_data_historical;


ASSETID
DATA_RECEIVED_ON


17085
23-AUG-2017 22:50

17085
25-AUG-2017 20:54

17085
25-AUG-2017 21:08

17085
31-AUG-2017 18:49

17085
29-SEP-2017 18:51


select * from customer_contacted_logs;


TICKETID
ASSETID
LAST_CONTACTED_ON


1785487
17085
28-SEP-2017 21:57

1785491
17085
16-OCT-2017 18:51


select assetid
     , data_received_on
     , (select min(last_contacted_on)
        from customer_contacted_logs c
        where c.assetid=a.assetid and last_contacted_on>=data_received_on)
       last_contacted_on
from asset_data_historical a;


ASSETID
DATA_RECEIVED_ON
LAST_CONTACTED_ON


17085
23-AUG-2017 22:50
28-SEP-2017 21:57

17085
25-AUG-2017 20:54
28-SEP-2017 21:57

17085
25-AUG-2017 21:08
28-SEP-2017 21:57

17085
31-AUG-2017 18:49
28-SEP-2017 21:57

17085
29-SEP-2017 18:51
16-OCT-2017 18:51


select assetid,data_received_on,ticketid,last_contacted_on
from (select assetid
           , data_received_on
           , (select min(ticketid) keep (dense_rank first order by last_contacted_on)
              from customer_contacted_logs c
              where c.assetid=a.assetid and last_contacted_on>=data_received_on)
             ticketid
      from asset_data_historical a)
     left outer join customer_contacted_logs using(ticketid,assetid);


ASSETID
DATA_RECEIVED_ON
TICKETID
LAST_CONTACTED_ON


17085
31-AUG-2017 18:49
1785487
28-SEP-2017 21:57

17085
25-AUG-2017 21:08
1785487
28-SEP-2017 21:57

17085
25-AUG-2017 20:54
1785487
28-SEP-2017 21:57

17085
23-AUG-2017 22:50
1785487
28-SEP-2017 21:57

17085
29-SEP-2017 18:51
1785491
16-OCT-2017 18:51

ASSETID	DATA_RECEIVED_ON
17085	23-AUG-2017 22:50
17085	25-AUG-2017 20:54
17085	25-AUG-2017 21:08
17085	31-AUG-2017 18:49
17085	29-SEP-2017 18:51

ASSETID	DATA_RECEIVED_ON	LAST_CONTACTED_ON
17085	23-AUG-2017 22:50	28-SEP-2017 21:57
17085	25-AUG-2017 20:54	28-SEP-2017 21:57
17085	25-AUG-2017 21:08	28-SEP-2017 21:57
17085	31-AUG-2017 18:49	28-SEP-2017 21:57
17085	29-SEP-2017 18:51	16-OCT-2017 18:51

ASSETID	DATA_RECEIVED_ON	TICKETID	LAST_CONTACTED_ON
17085	31-AUG-2017 18:49	1785487	28-SEP-2017 21:57
17085	25-AUG-2017 21:08	1785487	28-SEP-2017 21:57
17085	25-AUG-2017 20:54	1785487	28-SEP-2017 21:57
17085	23-AUG-2017 22:50	1785487	28-SEP-2017 21:57
17085	29-SEP-2017 18:51	1785491	16-OCT-2017 18:51