Get the last time before the call - SQL Query-CodePudding

I have here a set of 2 tables that I need to bash. First table is the list of time and date the customer contacted us, its not unique. The next table is the escalated call they made to us.

What I need to do is to show the date and time before the escalated call.

I can do simple left join based on customer ID, but having issue on the getting the last call. Hope that I can get answers explanation that I can use moving forward.

Here's my code so far:

Select a.customer id, a.contact_time, b.date of contact time as last_contact
from escalated_call a
left join all calls b on a.customer id = b.customer ID

CodePudding user response：

Just Use a Where Clause

Select  a.customerid, 
        a.contact_time, 
        b.DateOfContactTime as last_contact
from    escalated_call AS a LEFT JOIN Calls AS b on a.customerID = b.customerID
WHERE   a.contact_time < b.DateOfContactTime

CodePudding user response：

You just need an aggregate max here, you can also do it with a correlated subquery but it’s probably not worth it.

You may need to correct your column names, I’ve just guessed you have underscored instead of the spaces

Select a.customer_id
      ,a.contact_time
      ,max(b.date_of_contact_time) as last_contact
from escalated_call a
left join all_calls b 
  on a.customer_id = b.customer_ID 
Group by a.customer_id, a.contact_time

CodePudding user response：

From Oracle 12, you can use a LATERAL join and return the FIRST ROW ONLY:

SELECT ec.*, ac.dt
FROM   escalated_calls ec
       LEFT OUTER JOIN LATERAL (
         SELECT ac.dt
         FROM   all_calls ac
         WHERE  ac.customer_id = ec.customer_id
         AND    ac.dt <= ec.ct
         ORDER BY ac.dt DESC
         FETCH FIRST ROW ONLY
       ) ac
       ON (1 = 1)

Which, for the sample data:

CREATE TABLE all_calls(customer_id, dt) AS
SELECT 1, DATE '2019-12-24'   INTERVAL '00:00' HOUR TO MINUTE FROM DUAL UNION ALL
SELECT 1, DATE '2019-12-24'   INTERVAL '00:15' HOUR TO MINUTE FROM DUAL UNION ALL
SELECT 1, DATE '2019-12-24'   INTERVAL '00:35' HOUR TO MINUTE FROM DUAL UNION ALL
SELECT 1, DATE '2019-12-24'   INTERVAL '01:00' HOUR TO MINUTE FROM DUAL UNION ALL
SELECT 2, DATE '2019-12-24'   INTERVAL '00:00' HOUR TO MINUTE FROM DUAL UNION ALL
SELECT 2, DATE '2019-12-24'   INTERVAL '00:15' HOUR TO MINUTE FROM DUAL UNION ALL
SELECT 2, DATE '2019-12-24'   INTERVAL '00:35' HOUR TO MINUTE FROM DUAL UNION ALL
SELECT 2, DATE '2019-12-24'   INTERVAL '01:00' HOUR TO MINUTE FROM DUAL;

CREATE TABLE escalated_calls (customer_id, ct) AS
SELECT 1, DATE '2019-12-24'   INTERVAL '00:45' HOUR TO MINUTE FROM DUAL UNION ALL
SELECT 2, DATE '2019-12-24'   INTERVAL '00:05' HOUR TO MINUTE FROM DUAL;

Outputs:

CUSTOMER_ID CT DT

1 2019-12-24 00:45:00 2019-12-24 00:35:00

2 2019-12-24 00:05:00 2019-12-24 00:00:00

CUSTOMER_ID	CT	DT
1	2019-12-24 00:45:00	2019-12-24 00:35:00
2	2019-12-24 00:05:00	2019-12-24 00:00:00

db<>fiddle here

CodePudding user response：

You can also use a subquery in the select clause to solve this problem.

SELECT e.*
, ( SELECT max(a.Date_Of_Contact_Time) 
      FROM all_calls a 
       WHERE a.customer_id = e.customer_id 
        AND a.Date_Of_Contact_Time <= e.contact_time  
   ) AS correct_answer
FROM escalated_calls e
;