SQL Querying subscribers older than 6 months

Question

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?

Answer 1

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())