Oracle Query to Compare dates from Two Different tables I have these two below tables.  One is storing Data Received for an Asset and other one is when the Customer Representative contacted.

Asset_Data_Historical

+---------+------------------+
| AssetID | Data_Received_On |
+---------+------------------+
|   17085 | 8/23/2017 22:50  |
|   17085 | 8/25/2017 20:54  |
|   17085 | 8/25/2017 21:08  |
|   17085 | 8/31/2017 18:49  |
|   17085 | 9/29/2017 18:51  |
+---------+------------------+


Customer_Contacted_Logs

+----------+----------+-------------------+
| TicketID | Asset_ID | Last_Contacted_On |
+----------+----------+-------------------+
|  1785487 |    17085 | 9/28/2017 21:57   |
|  1785491 |    17085 | 10/16/2017 18:51  |
+----------+----------+-------------------+


My Problem is:
I need to find the data from the Customer_Contacted_Logs based on the Asset_data_Historical table.  

For Example:  for the AssetID 17085, the first Data Received on 8/23/2017 22:50. Now, query has to look into the Customer_Contacted_Logs for the data "Last_Contacted_on" Equal to greater than but not greater than next Data Received_On.

So, in this situation, the First Data received on 8/23/2017 22:50 and it will look for the data in Customer_contacted_log and the finds the date is 9/28/2017 21:57 and the same date will populate as the Contacted date is not greater than Data_Received_on. In the next instance, it will fetch the date 10/16/2017 18:51.

Here is an example, How I would like the query to fetch.

+---------+------------------+-------------------+
| AssetID | Data_Received_On | Last_Contacted_On |
+---------+------------------+-------------------+
|   17085 | 8/23/2017 22:50  | 9/28/2017 21:57   |
|   17085 | 8/25/2017 20:54  | 9/28/2017 21:57   |
|   17085 | 8/25/2017 21:08  | 9/28/2017 21:57   |
|   17085 | 8/31/2017 18:49  | 9/28/2017 21:57   |
|   17085 | 9/29/2017 18:51  | 10/16/2017 18:51  |
+---------+------------------+-------------------+


Please let me know,  if any questions.  

Thank you In advance.
There are other ways to do this but the simplest is with a subquery:




select assetid
     , data_received_on
     , (select min(last_contacted_on)
        from customer_contacted_logs c
        where c.assetid=a.assetid and last_contacted_on&gt;=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



If you need the ticket ID as well it becomes a more interesting problem — and the idiomatic Oracle way is with KEEP…FIRST:




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&gt;=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



db&lt;>fiddle here