I've been tinkering with this for some time and cannot get it to work.
I have a data extension with the fields: [EmailName], [EmailAddress], [JobID], [SubscriberKey], [DeliveredTime], [OpenDate]
I have the SQL QUERY built (see below) with the end goal to pull all e-mails sent in a timeframe. I also want to include either if/when they opened the e-mail. The below query has valid syntax and runs, but doesn't work to update the DE. It only works if I remove the join that adds the _Open table.
Any ideas as to an error with the syntax below? Thank you!
SELECT j.EmailName, s.EmailAddress, j.JobID, j.DeliveredTime, s.SubscriberKey, max(o.EventDate) as OpenDate
FROM _Job j
INNER JOIN _Sent se ON j.JobID = se.JobID
INNER JOIN _Subscribers s ON se.SubscriberKey = s.SubscriberKey
LEFT JOIN _Open o ON se.JobID = o.JobID
WHERE se.EventDate >= GetDate()-9 AND se.EventDate <= GetDate() -7
GROUP BY j.JobID, j.EmailName, j.DeliveredTime, s.EmailAddress, s.SubscriberKey