I query the _Opens data view which updates my
SubscribersMain Data extension with the latest email open dates, I have noticed that the query blanks out the
last_open_date when it shouldn't but cant figure out why, I suspect it happens only when the Subscriber already has a null value in the
last_opened_date field. I can see records that absolutely do have timestamps this month, become blank.
EDIT: It looks like none of the comparisons work, it always reverts to the ELSE field in the case statement.
EDIT 2: Actually it looks like even when there is a value in the column, and the aggregated EventDate has a value... the query will return a blank value.
Here is my query:
select sub.subscriberKey, (CASE WHEN dateadd(hour,6,MAX(o.EventDate)) > sub.last_opened_date THEN dateadd(hour,6,MAX(o.EventDate)) ELSE sub.last_opened_date END) AS last_opened_date FROM SubscribersMain sub INNER JOIN _Open o ON sub.SubscriberKey = o.SubscriberKey GROUP BY sub.SubscriberKey, Sub.last_opened_date HAVING MAX(o.EventDate) <> ''