0

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.

0
1

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>=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>=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<>fiddle here

2
  • A couple of things need to be noted. First, the proposed solutions assume that the tables are correctly designed with the date columns being defined as DATE. If they are not, they should be redesigned, but lacking that, all references to the date columns would need to be wrapped in an appropriate TO_DATE function. Second, proposed solution assumes that the time portion of a DATE column is significant to the business problem. If not, then the date columns should be wrapped in the TRUNC function, and allowance made for 'ties'. – EdStevens Mar 24 '19 at 13:11
  • Yes to the first — if only that was always safe to assume! I think it's safe to assume the second as hours/minutes are visible in the OPs data — though perhaps seconds are not. Anyway these are questions for the OP really. – Jack Douglas Mar 25 '19 at 8:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.