I'm needing to write a query that finds all opportunities on an account that closed within 90 days of an email record being clicked by a contact on the account.

My resulting table is currently displaying multiple rows for a given opportunity, because the client and their spouse may have received multiple emails that were clicked that marketed the opportunity.

I want to show a unique opportunity id and on the same row, display the email that was most recently clicked compared to the closedate.

Current query:

select o.id, 
from Opportunity_Salesforce o
    inner join Account_Salesforce a
        on o.accountid = a.id
    left join Contact_Salesforce c
        on c.accountid = a.id
    inner join et4ae5__IndividualEmailResult__c_Salesforce e
        on e.et4ae5__Contact__c = c.id
where ((e.Name like 'xyz%' 
    OR e.Name like 'abc%' 
    OR e.Name like 'etc%')
    and e.et4ae5__Opened__c = 'true')
    and (o.iswon = 'true'
    and o.closedate < dateadd(day, 91, et4ae5__DateOpened__c)
    and o.closedate >= et4ae5__DateOpened__c)


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.