0

I am trying to query data from an existing data extension into a new data extension and filter out the subscribers that were added within the last six months. The existing data extension does not have a date joined column, thus I am trying to query the _Subscribers data view to get this info. My query is as follows:

SELECT *
FROM Data_Extension
WHERE CAST(DATEADD(MONTH, -6, GETDATE()) as smalldatetime) <=
(
    SELECT DateJoined
    FROM _Subscribers
)

The syntax is valid, but somehow I am getting 0 subscribers in my target data extension which is not possible. How can I fix my query?

  • 1
    Remember to add a prefix of "ENT" if you're running the query from a child BU : ENT._Subscribers – jclobro Jul 18 at 7:26
5

Instead of a sub query, you will want to use a join to the subscriber data view. For example:

Select de.* from mydataextension de 
Join _subscribers s 
On s.subscriberkey = de.subscriberkey
Where s.datejoined <= dateadd(m,-6,getdate())
  • thanks, your code worked after I changed the JOIN line to JOIN ENT._Subscribers s as suggested by @jclobro – Intern Jul 18 at 13:34

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.