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.
Best Answer
There are other ways to do this but the simplest is with a subquery:
If you need the ticket ID as well it becomes a more interesting problem — and the idiomatic Oracle way is with
KEEP…FIRST
:db<>fiddle here