1

We have Salesforce data coming into Marketing Cloud (exacttarget) via Data Stream and have a query using the Synchronized data to create a Shared Data Extension. However, one of my queries isn't giving the correct number of records (223,256) compared to a report in SF (317,673).

My Query looks like this:

SELECT
o.id as [Opportunity ID],
...(fields)...

CASE WHEN adloc.Name != '' THEN adloc.Name
ELSE loc.Name END as [Opportunity Location],
...(more CASE statements)...

FROM Opportunity_Salesforce o

LEFT JOIN Location__c_Salesforce adloc
on o.admitted_location__c = adloc.id

...(more LEFT JOIN statements)...

WHERE o.closedate > GETDATE() AND o.isclosed = 'FALSE'

I have a nervous feeling that the multiple LEFT JOINs might be my problem (eg it only gives results where all are there?), I'm not a SQL master by any means.

The Salesforce report has the following Filters:

Opportunity Close Date greater than TODAY 
AND Closed equals False

1 Answer 1

0

The left join should be fine.. it'll return null if no match is found.

The issue may be with the time portions of the dates. You can cast SFMC dates as date to strip the time portion from the timestamp. This will effectively compare dates to dates, not timestamps to timestamps. Like this:

SELECT
o.id as [Opportunity ID],
...(fields)...

CASE WHEN adloc.Name != '' THEN adloc.Name
ELSE loc.Name END as [Opportunity Location],
...(more CASE statements)...

FROM Opportunity_Salesforce o

LEFT JOIN Location__c_Salesforce adloc
on o.admitted_location__c = adloc.id

...(more LEFT JOIN statements)...

WHERE cast(o.closedate as  date) > cast(GETDATE() as date) 
AND o.isclosed = 'FALSE'

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.