CODEWITHSUNDEEP
sql-servert-sql
Chris
Chris

Reputation: 1766

SQL Server Match Closest Record With Another Table

I have two tables, one with the time a customer spoke with a colleague and another where they have made a purchase however I can't figure out how to tie the closest purchase to a particular colleague

For example

Table 1 - Colleague Interactions

[ColleagueID]  [DateOfInteraction]  [CustomerID]
------------------------------------------------
       A        2018-11-22 12:00         123
       B        2018-11-22 12:02         123

Table 2 - List of Purchases

[PurchaseID]  [DateOfPurchase]  [PurchaseOutcome]  [CustomerID]
----------------------------------------------------------------
     1         2018-11-22 12:01      FAIL              123
     2         2018-11-22 12:03      SUCCESS           123

What I want to do with the above is to tie these two records together, initially I do this on the CustomerID but this obviously create duplication as the customerID appears twice in Table 2 and Table 1. I then try to narrow to say where DateOfInteraction < DateOfPurchase which will eliminate the first record however I am unsure on how to match the second?

The results should look like

[ColleagueID]  [DateOfInteraction]  [CustomerID]  [PurchaseID]  [DateOfPurchase]  [PurchaseOutcome]
---------------------------------------------------------------------------------------------------
       A        2018-11-22 12:00        123            1         2018-11-22 12:01      FAIL
       B        2018-11-22 12:02        123            2         2018-11-22 12:03      SUCCESS

Upvotes: 1

Views: 29

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269893

I think apply does what you want:

select p.*, i.*
from purchases p outer apply
     (select top (1) i.*
      from interactions i
      where i.customer_id = p.customer_id and
            i.DateOfInteraction < p.DateOfPurhase
      order by i.DateOfInteraction desc
     ) i;

Upvotes: 1

Related Questions