Geert
4/30/2020 - 3:08 PM

select non opens by jobid - extended

// Added left joins + where statements


select distinct 
s.subscriberKey as contactid, 
c.Email,
a.Id AS Accountid18,
a.[UMAX_AccountNr__c] AS [UMAX Account Number]
from _sent s
LEFT JOIN Contact_Salesforce AS c ON s.subscriberkey = c.Id
LEFT JOIN Account_Salesforce AS a ON c.AccountId = a.Id
where s.subscriberKey != '' and c.email IS NOT NULL AND a.id IS NOT NULL and a.[UMAX_AccountNr__c] IS NOT NULL and s.JobID = 677589 OR s.JobID = 677590 OR s.JobID = 677591
and s.SubscriberKey NOT IN (
  SELECT o.SubscriberKey 
  FROM _Open o  
  WHERE o.JobID = s.JobID
  AND o.SubscriberKey = s.SubscriberKey
)