Show the last call made prior to escalation

Transact-SQL
1

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 prior to escalation 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

tables
tables1003×212 38.5 KB

2 
SELECT 
    e.Customer_Id,
    e.Contct_Time,
    s.Correct_Answer
FROM
    EscalatedCall AS e
    OUTER APPLY
    (
        SELECT TOP (1) a.Date_Of_Contact_Time AS Correct_Answer
        FROM
            All_Calls AS a 
        WHERE
            a.Customer_Id = e.Customer_Id
            AND a.Date_of_Contact_Time < e.Contact_Time
        ORDER BY
            a.Date_of_Contact_Time DESC
    ) AS s;
3

Another way of doing this

-- Performance
-- Shorter Code
-- Easy to understand

create data script

drop table #All_Calls
create table #All_Calls (Cust_ID int , Con_time datetime)
insert into #All_Calls select 1 , '12/24/2019 00:00'
insert into #All_Calls select 1 , '12/24/2019 00:15'
insert into #All_Calls select 1 , '12/24/2019 00:35'
insert into #All_Calls select 1 , '12/24/2019 01:00'
insert into #All_Calls select 2 , '12/24/2019 00:00'
insert into #All_Calls select 2 , '12/24/2019 00:15'
insert into #All_Calls select 2 , '12/24/2019 00:35'
insert into #All_Calls select 2 , '12/24/2019 01:00'
select * from #All_Calls

drop table #Esc_Calls
create table #Esc_Calls ( Cust_ID int , Con_Time datetime )
insert into #Esc_Calls select 1 , '12/24/2019 00:45'
insert into #Esc_Calls select 2 , '12/24/2019 00:00'
select * from #Esc_Calls

SELECT 
      a.cust_id 
    , max(a.con_time) 
FROM
   #All_Calls a 
       join 
   #Esc_Calls b 
      on 
	      a.Cust_ID = b.Cust_ID and a.Con_time <= b.Con_Time
GROUP BY 
      a.Cust_ID

image