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'))

this isn't going to look at dates within two weeks, and the local max of the group. this is finding the absolute max in the finance table (regardless of joins). the bolded is a single date for the entire table regardless of all conditions.