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
  • 1
    Remember to add a prefix of "ENT" if you're running the query from a child BU : ENT._Subscribers
    – jc_mc
    Jul 18, 2019 at 7:26

1 Answer 1

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())
1
  • thanks, your code worked after I changed the JOIN line to JOIN ENT._Subscribers s as suggested by @jclobro
    – Intern
    Jul 18, 2019 at 13:34

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .