0

I'm trying to create a query activity to get who opened any email yesterday and update it to a data extension on Exact Target. I'm using the following code

SELECT 
 SubscriberID as SubscriberKey,
 case when DATEPART(HOUR, EventDate) BETWEEN 0 AND 6 then '6'
 when DATEPART(HOUR, EventDate) BETWEEN 6 and 12 then '12'
 when DATEPART(HOUR, EventDate) BETWEEN 12 and 18 then '18'
 when DATEPART(HOUR, EventDate) BETWEEN 18 and 24 then '24'
 end open0_L
FROM _Open 
WHERE 1=1
and EventDate >= DATEADD (day, -1, GETDATE())

But, I'm getting errors on the last line with the update method. If I remove this line:

and EventDate >= DATEADD (day, -1, GETDATE())

It works for update. How can I fix this?

EDIT These are the fields on the target data extension:

SubscriberKey Text (Primary Key)
EmailAddress  EmailAddress
open0_L       Number
open1_L       Number
open2_L       Number
open3_L       Number
open4_L       Number
open5_L       Number
open6_L       Number
open7_L       Number
open8_L       Number
open9_L       Number
open10_L       Number

EDIT2

I edited the query, to get only distinct subscriberID, but still get error.

SELECT 
     distinct SubscriberID as SubscriberKey,
     case when DATEPART(HOUR, EventDate) BETWEEN 0 AND 6 then '6'
     when DATEPART(HOUR, EventDate) BETWEEN 6 and 12 then '12'
     when DATEPART(HOUR, EventDate) BETWEEN 12 and 18 then '18'
     when DATEPART(HOUR, EventDate) BETWEEN 18 and 24 then '24'
     end open0_L
    FROM _Open 
    WHERE 
EventDate >= DATEADD (day, -1, GETDATE())

EDIT3 I'm removing the time part of Event date now. But till get errors.

SELECT 
     distinct SubscriberID as SubscriberKey,
     case when DATEPART(HOUR, EventDate) BETWEEN 0 AND 6 then '6'
     when DATEPART(HOUR, EventDate) BETWEEN 6 and 12 then '12'
     when DATEPART(HOUR, EventDate) BETWEEN 12 and 18 then '18'
     when DATEPART(HOUR, EventDate) BETWEEN 18 and 24 then '24'
     end open0_L
    FROM _Open 
    WHERE 1=1
    and CONVERT(date, EventDate) >= DATEADD (day, -1, GETDATE())
  • What error? At validation or runtime? – Adam Spriggs Oct 27 '15 at 15:14
  • What's columns are in the target data extension? What is the primary key on the target data extension? – Adam Spriggs Oct 27 '15 at 15:17
  • @AdamSpriggs I updated the question with the fields on the target data extension. The error is on runtime. I don't see a message of the error. I'm running the query on Automation Studio. Where can I see the error message? – Filipe Ferminiano Oct 27 '15 at 17:02
1

There's no need for the 1=1 in your query:

SELECT 
 SubscriberID as SubscriberKey,
 case when DATEPART(HOUR, EventDate) BETWEEN 0 AND 6 then '6'
 when DATEPART(HOUR, EventDate) BETWEEN 6 and 12 then '12'
 when DATEPART(HOUR, EventDate) BETWEEN 12 and 18 then '18'
 when DATEPART(HOUR, EventDate) BETWEEN 18 and 24 then '24'
 end open0_L
FROM _Open 
WHERE EventDate >= DATEADD (day, -1, GETDATE())

You're assigning open0_L as Text, but storing it as a Number. That could potentially cause a runtime error.

Second, you didn't mention what the Primary Keys were on your Data Extension. You could be violating it since _Open can return multiple rows for a given subscriber. I'd expect you'd want SubscriberKey + EventDate as the Primary Key to accommodate your results.

SFMC Support can give you details about query failures -- as can certain SFMC Partners with access to the Error Log.

  • I edited the question with the PrimaryKey and I also edited my query with a distinct on the primarykey, but I'm still getting errors – Filipe Ferminiano Oct 27 '15 at 18:25
  • Just setting a PK on SK isn't enough. You'll need another field, since a subscriber have _Open records multiple times on a single day. You'll also need to match data types and precision in your target data extension with the _Open schema. – Adam Spriggs Oct 27 '15 at 18:30
  • But i just want to check if he opened yesterday or not. I don't need to know if how many times he opened – Filipe Ferminiano Oct 27 '15 at 18:41
  • Then you'll need to collapse it down in your query. EventDate is date + time. – Adam Spriggs Oct 27 '15 at 18:54
  • 1
    Have the query target a new data extension with no primary key and you will see what Adam is referring to. You results will most likely contain multiple entries for a single subscriber. – David Devoy Nov 11 '15 at 19:14
0

This is a SOQL query in Apex, correct? Would this suit your needs?

and EventDate >= LAST_N_DAYS:1

or

and EventDate >= YESTERDAY

Alternatively, since you're probably making this query in Apex and you may want exact timing instead of including the entire day, you could add a variable in your code before the query:

DateTime last24hours = System.now().addHours(-24);

and then your query's where clause would include:

and EventDate >= :last24hours
  • no, it's on query activity. I don't know if it's on Apex. YESTERDAY is not defined. And on ther other options that you mentioned, I get syntax error. – Filipe Ferminiano Oct 27 '15 at 14:49
  • 1
    Gotcha. I mistook your query for SOQL. My solution won't work for this, and unfortunately I'm not familiar enough with ExactTarget/AMPscript to assist here. – JCD Oct 27 '15 at 14:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.