0

I am currently constructing a query that will add people into a data extension when their event_date is 28, 21, 14 or 7 days away. I currently use

WHERE hasMadeApplication = 'False'
AND [Event_StartDate] >= DateAdd(day,28,cast(GetDate() as date))
AND [Event_StartDate] < DateAdd(day,29,cast(GetDate() as date))

to get those 28 days away, but how would I write the rest of it to add those people 21 or 14 days away? I'm not sure whereabouts the 'ORs' and 'ANDs' need to go. I have tried

WHERE hasMadeApplication = 'False'
AND [Event_StartDate] >= DateAdd(day,28,cast(GetDate() as date))
AND [Event_StartDate] < DateAdd(day,29,cast(GetDate() as date))
OR WHERE hasMadeApplication = 'False'
AND [Event_StartDate] >= DateAdd(day,21,cast(GetDate() as date))
AND [Event_StartDate] < DateAdd(day,22,cast(GetDate() as date))
OR WHERE hasMadeApplication = 'False'
AND [Event_StartDate] >= DateAdd(day,14,cast(GetDate() as date))
AND [Event_StartDate] < DateAdd(day,15,cast(GetDate() as date))

But that doesn't transfer any records. Clearly my syntax is wrong somewhere.

  • why you're not using Data Filter? – Palash Rai Feb 10 '17 at 10:14
  • It's being used in an automation daily to be added to a journey. – TCassa Feb 10 '17 at 10:21
  • Also it only copies a few fields, not the whole data extension. – TCassa Feb 10 '17 at 10:50
  • 1
    WHERE hasMadeApplication = 'False' AND ( ([Event_StartDate] >= DATEADD(DAY, 28, CAST(GETDATE() AS date)) AND [Event_StartDate] < DATEADD(DAY, 29, CAST(GETDATE() AS date))) OR ([Event_StartDate] >= DATEADD(DAY, 21, CAST(GETDATE() AS date)) AND [Event_StartDate] < DATEADD(DAY, 22, CAST(GETDATE() AS date))) OR ([Event_StartDate] >= DATEADD(DAY, 14, CAST(GETDATE() AS date)) AND [Event_StartDate] < DATEADD(DAY, 15, CAST(GETDATE() AS date))) ) – Palash Rai Feb 10 '17 at 11:03
  • hope this may work. – Palash Rai Feb 10 '17 at 11:08
3

Patash's solution in the comments section is correct. The issue with TCassa's original query was that is required parenthesis around the OR conditions.

An alternative solution would be to use DateDiff() function, rather than DateAdd(). This makes it a bit easier to check for additional dates in the query without using OR statements.

WHERE hasMadeApplication = 'False' 
AND DateDiff(Day,getDate(),[Event_StartDate]) 
in (28, 21, 14 , 7 )

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.