How do I use just the first value that satisfies a condition in my join?

I'm trying to think what the most efficient way to do this is, hopefully you can help. I have two tables, one is for Customer Call data (customer calls in to a customer service number), the other is invoice data. They come from different sources so there is no shared PK-FK relationship. Here is some sample data:

CallData

CallId	PhoneNumber	Source	CallDate
12345	555-555-5555	internet	2023-11-06
12346	555-666-6666	referral	2023-01-01
12347	555-666-6666	referral	2023-02-02
12348	555-666-6666	internet	2023-05-17
12349	555-777-7777	referral	2023-10-31
12350	555-777-7777	internet	2023-11-03

Invoice

InvoiceId	PhoneNumber	Amount	InvoiceDate
11111	555-555-5555	250.56	2023-01-01
22222	555-666-6666	376.67	2023-02-01
33333	555-666-6666	761.09	2023-06-02
44444	555-666-6666	316.23	2023-07-03
55555	555-777-7777	145.33	2023-10-01
66666	555-777-7777	986.92	2023-11-01

I need to be able to join these two together based on the PhoneNumber. But I only care about records where the CallData.Source = 'referral' so that eliminates all but 3 CallData records from above. I then want to sum all the Invoice.Amount values but only if the InvoiceDate is greater than at least ONE of the CallDates.

So consider the CallData records with PhoneNumber 555-666-6666, there are 2 that are referral types and their CallDates are:

2023-01-01
2023-02-02

And in the Invoice table there are 3 invoices with InvoiceDates of:

2023-02-01
2023-06-02
2023-07-03

All I care about is that all 3 InvoiceDates are AFTER at least one of the matching CallDates (in this case 2023-01-01), so I would sum all three and get 1453.99.

Please let me know if you have any further questions, thanks

Solution

You could use MIN() to get the minimum CallDate per PhoneNumber:

SELECT Calls.*, SUM(Invoice.Amount) AS Amount
FROM (
    SELECT PhoneNumber, MIN(CallDate) AS MinCallDate
    FROM CallData
    WHERE Source = 'referral'
    GROUP BY PhoneNumber
) Calls
JOIN Invoice
    ON Invoice.PhoneNumber = Calls.PhoneNumber 
   AND Invoice.InvoiceDate >= Calls.MinCallDate
GROUP BY Calls.PhoneNumber;

Output:

PhoneNumber	MinCallDate	Amount
555-666-6666	2023-01-01	1453.99
555-777-7777	2023-10-31	986.92

Here's a db<>fiddle.