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.

5
  • why you're not using Data Filter?
    – Palash Rai
    Commented Feb 10, 2017 at 10:14
  • It's being used in an automation daily to be added to a journey.
    – TCassa
    Commented Feb 10, 2017 at 10:21
  • Also it only copies a few fields, not the whole data extension.
    – TCassa
    Commented Feb 10, 2017 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
    Commented Feb 10, 2017 at 11:03
  • hope this may work.
    – Palash Rai
    Commented Feb 10, 2017 at 11:08

1 Answer 1

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 )

You must log in to answer this question.

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