0

I am trying to grab email address from different sources for selected Account IDs. I have all email address in Contact table those are grabbed from Account and Opportunity table. So task is first I need to look for Email address in contact table based on Account IDs if Email found then grab else look for other source which is Opportunity if found grab it.

So priority is first grab email where source is "Account" if email is blank for Account then go and look for source as "Opportunity"

I tried this query, it successfully bring all emails associated with Accountd IDs where source is Account.

select t1.uuid, t1.accountid as account_id, t2.email_address, t2.source, t2.store_name 
from mpfmaster2 t1

outer apply
(select TOP 1 t2.*
from global_contact_table_master t2
where t1.accountid = t2.account_id
and t2.source in ('sfdc_account_primary', 'sfdc_account',)) t2

But the problem is how Do i run the other query to grab email address where Email address is blank. I tried the below query, It does brings in email address of only missing AccountID's. I wanted to bring in all emails from above Query and then check if email is blank then go search for source as Opportunity?

select t1.uuid, t1.account_id, t2.email_address, t2.source, t2.store_name  
from mpf_v2_account_primary t1

outer apply
(select TOP 1 t2.*
from global_contact_table_master t2
where t1.account_id = t2.account_id
and t2.source in ('sfdc_opportunity')) t2

where t1.email_address is null

Any idea how do i do this?

0
0

If I understand your problem, you can use the COALESCE function in T-SQL.

2
  • 1
    This is more of a comment than an answer in its current form. Please either elaborate more on it or move this to a comment. – Gortonington Oct 2 '19 at 13:43
  • COALESCE will pick whichever the first record come. I want to check if associate ID have email address where source value is Account then pick else look for other source value such as Opportunity. Besides If the ID have two emails with different source such as source value as account and opportunity then I want to pick only Account source email. – Sohail Oct 2 '19 at 14:09

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.