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())
3
  • What error? At validation or runtime? Oct 27, 2015 at 15:14
  • What's columns are in the target data extension? What is the primary key on the target data extension? Oct 27, 2015 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? Oct 27, 2015 at 17:02

2 Answers 2

2

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.

6
  • 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 Oct 27, 2015 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. Oct 27, 2015 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 Oct 27, 2015 at 18:41
  • Then you'll need to collapse it down in your query. EventDate is date + time. Oct 27, 2015 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. Nov 11, 2015 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
2
  • 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. Oct 27, 2015 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, 2015 at 14:57

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .