Selecting max date before a date pulled in from another table..possible?

Transact-SQL
1

So basically you can see my join below. I think this should be relatively easy to fix, but I'm drawing a blank.

I'm joining the finance table twice, once where I pull in the date before the email (works great) and once where I try to pull the most recent date that happens within 2 weeks of the email being sent.

The problem is that the second join will pull the max date regardless of my second join condition.

Anyone know how to properly write this syntax? Again, the second finance join I do ideally pulls the max date from the sent email date in the A aliased table.

from
odc_marketing.loc_mapping_1 a
INNER JOIN sf_opportunity o ON o.id = a.opp_coalescer
INNER JOIN finance C ON c.loan_id = o.loanidc
AND c.as_of_date::date = a.sent_email_date::date - interval '1 day'

The second join below is the problem :confused:

INNER JOIN finance d ON d.loan_id = o.loanidc
AND (d.as_of_date = (SELECT max(as_of_date) from finance)
And d.as_of_date <= (a.sent_email_date::date + interval '2 weeks'))

2

perhaps the condition should be <= ?

INNER JOIN finance d ON d.loan_id = o.loanidc
AND (d.as_of_date = (SELECT max(as_of_date) from finance)
3

Try CROSS APPLY (or OUTER APPLY, if the row may not be found) rather than a JOIN:

CROSS APPLY (
    SELECT TOP (1) *
    FROM finance f
    WHERE f.loan_id = o.loanidc
      AND f.as_of_date <= (a.sent_email_date::date + interval '2 weeks'))
    ORDER BY f.as_of_date DESC
) AS d