0

In my query activity WHERE clause I want to check the value of a CreatedDate. If the current day is Monday I want to bring in records where the CreatedDate is on the previous three days (preceding Friday/Saturday/Sunday). If the current day is anything else just check the previous day.

Something like this... except it appears a OR statement after THEN in a CASE isn't possible. Any ideas of another approach?

WHERE GoalCreationDate = 
CASE WHEN
    DATENAME(weekday,GETDATE()) = 'Monday' THEN 
    (DATEADD(day,-3, convert(date, GETDATE())) OR 
    DATEADD(day,-2, convert(date, GETDATE())) OR
    DATEADD(day,-1, convert(date, GETDATE())))
ELSE
    DATEADD(day,-1, convert(date, GETDATE()))
END
1

Yep, that's not a valid CASE statement, you need to rework the logic for what you're attempting.

Reconsider how you're looking at the problem. In effect, your default is to look one day back, except for Mondays, where you look back three days. Something like this could work to meet that requirement:

WHERE GoalCreationDate BETWEEN (
CASE 
    WHEN DATENAME(weekday,GETDATE()) = 'Monday' THEN DATEADD(dd,-3,GETDATE() 
    ELSE DATEADD(dd,-1, GETDATE()) 
END
) AND GETDATE()

Don't forget to adjust your date calculation for SFMC's timezone vs your data's timezone, if required.

| improve this answer | |
  • Brilliant! Exactly what I needed thanks – samadhi Nov 13 at 7:23

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