0
    SELECT DISTINCT c.Email,  c.FName
FROM ENT.Contacts c WITH(NOLOCK)
INNER JOIN ENT.RegistrationData r WITH(NOLOCK)
ON c.Email  =  r.RegistrationContactEmail
LEFT Join ENT._Subscribers s  WITH(NOLOCK)
ON c.Email =  s.SubscriberKey
AND s.status NOT IN ('unsubscribed','held')

WHERE

(
    (
        (
            (
                c.Email IN
                (
                    SELECT DISTINCT 
                        s.SubscriberKey AS Email
                    FROM 
                        _Open s
                    WHERE
                        datediff(mm,s.EventDate, getdate()) <= 3
                )
            )
            OR
            (
                c.Email IN
                (
                    SELECT DISTINCT 
                        s.SubscriberKey AS Email
                    FROM 
                        _Click s
                    WHERE
                        datediff(mm,s.EventDate, getdate()) <= 3
                )
            )
        )

        AND

        (
            (
                r.RegistrationEmailOptStatus  =  '1' AND
                r.RegistrationEventType  =  'Wedding' AND
                r.RegistrationEventRole IN ('Bride','Other','Bridesmaid','Mother Of the Bride') AND
                r.RegistrationCountry IN ('USA') AND
                r.RegistrationEventDate < '2014-05-31'
            )

            OR

            (
                r.RegistrationEmailOptStatus  =  '1' AND
                r.RegistrationEventType  =  'Prom' AND
                r.RegistrationEventRole ='Prom' AND
                r.RegistrationCountry IN ('USA') AND
                r.RegistrationEventDate BETWEEN '2014-01-01' AND '2015-12-31'
            )
        )


    )

    OR

    (
        r.RegistrationEmailOptStatus  =  '1' AND
        r.RegistrationEventType  =  'Wedding' AND
        r.RegistrationEventRole IN ('Bride','Other','Bridesmaid','Mother Of the Bride') AND
        r.RegistrationCountry IN ('USA') AND
        r.RegistrationEventDate BETWEEN '2015-05-01' AND '2015-05-31'
    )
)
3
  • 2
    You should post this on StackOverflow, this isn't SF related. – Brian Mansfield Jun 11 '15 at 20:59
  • I'm writing this query in the Salesforce Marketing Cloud.... wrong place? – Lukacko Jun 11 '15 at 21:07
  • If it was SOQL you were looking to improve, it would be appropriate. Perhaps someone will help you here anyway, but you might get a faster response on SO – Brian Mansfield Jun 11 '15 at 21:11
2

My SQL skills are a bit rusty, and I don't have an environment to test this on, but here goes!

Instead of doing the below, and then filtering in the where clause:

SELECT DISTINCT c.Email,  c.FName
FROM ENT.Contacts c WITH(NOLOCK)
  INNER JOIN ENT.RegistrationData r WITH(NOLOCK)
    ON c.Email  =  r.RegistrationContactEmail

You could try something like:

SELECT c.Email, c.Fname
FROM ENT.Contacts c WITH(NOLOCK)
  INNER JOIN (SELECT DISTINCT Email
              FROM (SELECT DISTINCT o.SubscriberKey AS Email
                    FROM _Open o
                    WHERE datediff(mm,s.EventDate, getdate()) <= 3
                    UNION
                    SELECT DISTINCT c.SubscriberKey AS Email
                    FROM _Click c
                    WHERE datediff(mm,s.EventDate, getdate()) <= 3)) e
    ON c.Email = e.Email

You of course will need to go and join the rest of the tables in a similar fashion. In theory (once again, not using joins for ~3 years now since I've been using SOQL/Salesforce) what should happen is that your Contacts table will be filtered down by the Distinct unioned queries - rather than returning a giant dataset and expecting the where clause to step through the individual records to see if they match the where criteria, and managing the distinct records.

And as @Brian Mansfield said, you will have better luck on other communities on SO.

1
  • 1
    I will give that a shot thanks! My fallback is to just break it apart into 2 queries, which works fine. But I'd prefer to have it in one. – Lukacko Jun 11 '15 at 22:25

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