0

I have a query activity that is returning rows that it shouldn't I'm guessing there is a problem with my sql but cannot figure it out. The idea of the query is to create a 'dormant' list, with these parameters:

(

If they subscribed more than 89 days ago AND last opened between 89 and 390 days ago, include them

OR

If have no recorded opens AND subscribed between 89 and 390 days ago, include them

)

AND

Their last CTASTay date is more than 89 days ago or is null

Example data:

The below subscriber is being returned by my query. But it should not as DateSubscribedEmail is only a few days ago:

  • SubscriberKey: 1234
  • DateSubscribedEmail: Jun 16 2020 8:07PM
  • LastEmailOpenCommercial: null
  • LastCTAStayDate: null

The date values are simple date/time values in a data extension. I have looked up this particular subscriber in the source data extension and can confirm the date/time values are all correct.

I can't figure out where my query is wrong. As always, I'm guessing I'm missing something obvious!

SQL Query:

SELECT *
FROM Subscribers_ALL
WHERE
    (
        (
            (
                CAST(DateSubscribedEmail AS DATE) < CAST(DATEADD(dd, -89, CURRENT_TIMESTAMP) AS DATE)
                AND
                (
                    CAST(LastEmailOpenCommercial AS DATE) < CAST(DATEADD(dd, -89, CURRENT_TIMESTAMP) AS DATE)
                    AND
                    CAST(LastEmailOpenCommercial AS DATE) > CAST(DATEADD(dd, -390, CURRENT_TIMESTAMP) AS DATE)
                )
            )
            OR
            (
                (
                    CAST(DateSubscribedEmail AS DATE) < CAST(DATEADD(dd, -89, CURRENT_TIMESTAMP) AS DATE)
                    AND
                    CAST(DateSubscribedEmail AS DATE) > CAST(DATEADD(dd, -390, CURRENT_TIMESTAMP) AS DATE)
                )
                AND 
                LastEmailOpenCommercial = '' OR LastEmailOpenCommercial IS NULL
            )
        )
        AND
        (
            CAST(LastCTAStayDate AS DATE) < CAST(DATEADD(dd, -89, CURRENT_TIMESTAMP) AS DATE)
            OR
            (LastCTAStayDate = '' OR LastCTAStayDate IS NULL)
        )
    
    )
1

You need to place this line in parenthesis:

LastEmailOpenCommercial = '' OR LastEmailOpenCommercial IS NULL

Resulting in this line:

(LastEmailOpenCommercial = '' OR LastEmailOpenCommercial IS NULL)

Without the brackets, you're asking for anyone with a LastEmailOpenCommercial of NULL, irrespective of their DateSubscribedEmail.

| improve this answer | |
  • That did the trick. Legend, thanks! – Ben Jun 21 at 22:47

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.