0

I'm sorry for the post title. I wasn't sure how to word that... I have a query that is working fine:

SELECT
j.SubscriberKey,
j.JobID,
j.EventDate
from _Sent j
where
DATEPART(m, j.EventDate) = DATEPART(m, DATEADD(m, -1, GETDATE()))
AND DATEPART(yyyy, j.EventDate) = DATEPART(yyyy, DATEADD(m, -1, GETDATE()))

But I need to get another value c.Name in table Campaign_Salesforce, but in order to match I need to match values in yet another table Lead_Salesforce.

So Lead_Salesforce has a column Id which is the same as the SubscriberKey in my query above. Campaign_Salesforce has an Id column which matches the column Consumer_newsletter__c in Lead_Salesforce. Hopefully the picture below can help.

enter image description here

0

You would need a subquery to connect Lead_Salesforce and Campaign_Salesforce,

Try this:

SELECT
j.SubscriberKey,
j.JobID,
j.EventDate,
x.name
from _Sent j


inner join (SELECT 

            l.id
            ,c.Name

             FROM 

             Lead_Salesforce l 

             inner join Campaign_Salesforce c 

             ON l.Consumer_newsletter__c= c.id)  x

ON j.SubscriberKey = x.id 

where
DATEPART(m, j.EventDate) = DATEPART(m, DATEADD(m, -1, GETDATE()))
AND DATEPART(yyyy, j.EventDate) = DATEPART(yyyy, DATEADD(m, -1, GETDATE()))

For optimisation you might want to might want to perform the subquery in a staging table.

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.