SQLTeam.com | Weblogs | Forums

Show the last call made prior to escalation

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 

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;