Exacttarget query to gather all email addresses that have opened in the past x month

Question

I'm not even at a level of novice in SQL but I wanted to know what the query would be to put into a data extension all emails that opened an email in the past month. Also, a query that will let me select the JOBID to filter with.

TIA

Answer 1

You can also do an inner join on the _Job System Data View (on JobId) if you need the email name and send time (PickupTime) in your results.

SELECT
    o.JobID
    , o.ListID
    , o.BatchID
    , o.SubscriberID
    , o.SubscriberKey
    , o.EventDate
    , o.Domain
    , o.IsUnique
    , j.pickuptime
    , j.emailName
FROM _Open as o with (nolock)
INNER JOIN _Job as j with (nolock) on (j.jobid = o.jobid)
WHERE
    j.pickuptime >= dateadd(day,getDate(),-30)

Here are the details on the System Data Views:

Query Activity (System Data Views)

The documentation doesn't really spell out the relationships between the views, but it's generally JobID or SubscriberID.

Answer 2

   SELECT
        JobID,
        ListID,
        BatchID,
        SubscriberID,
        SubscriberKey,
        EventDate,
        Domain,
        IsUnique
    FROM
        _Open WITH (NOLOCK)
    WHERE
        JobID = 12345 OR
        DATEDIFF(day,EventDate,GETDATE()) <= 30