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