case statement in a where clause

Question

I am trying to create a where statement that considers what day of the week it is and then filters according. For example, if the day I am pulling the data is a Wednesday, I only want to pull data for Monday - Wednesday of that week. If it is a Friday, then Monday - Friday and so on.

I tried doing this using a case in my where clause, but I can't get it to work.

select 
    uuid,
    acquisition_campaign,
    REFERRAL_PROMO,
    channel,
    partner,
    created_at::date as created_date
from CONSUMER_TMP
where created_date between case DAYOFWEEK(getdate())
                                when 0 then (getdate()::date) and (getdate()::date)
                                when 1 then (DATEADD('day', -1, getdate())) and (getdate())
                                when 2 then (DATEADD('day', -2, getdate())) and (getdate())
                                when 3 then (DATEADD('day', -3, getdate())) and (getdate())
                                when 4 then (DATEADD('day', -4, getdate())) and (getdate())
                                when 5 then (DATEADD('day', -5, getdate())) and (getdate())
                                when 6 then (DATEADD('day', -6, getdate())) and (getdate())
                                else (DATEADD('day', -7, getdate())) and (getdate())
                           end

Answer 1

I didn't test it but I think this would work.

One issue is you cannot use custom column name in where clause, so first you'll have to change that to actual column name. Second you'll need to change the and in your query like below:

where created_at::date between case DAYOFWEEK(getdate())
                                when 0 then (getdate()::date)
                                when 1 then (DATEADD('day', -1, getdate()))
                                when 2 then (DATEADD('day', -2, getdate()))
                                when 3 then (DATEADD('day', -3, getdate()))
                                when 4 then (DATEADD('day', -4, getdate()))
                                when 5 then (DATEADD('day', -5, getdate()))
                                when 6 then (DATEADD('day', -6, getdate()))
                                else (DATEADD('day', -7, getdate()))
                           end and (getdate())

Because the and in your case needs to be outside the case like between something and other_thing, for example when DAYOFWEEK(getdate()) is 0 then it checks if created_date between (getdate()::date) and (getdate()) and so on.

---

Edit:

As @Simeon Pilgrim says in comments you can use in snowflake the column name from your select.

And I think you can even simplify your query even more, you don't need a case at all, you can do like:

where created_date between (DATEADD('day',-(DAYOFWEEK(getdate())),getdate())) and (getdate())

So if day of week is 0 it will return between getdate() and getdate(), because adding -0 days adds 0 days, if it's 1 it will return between getdate() - 1 and getdate() and so on.

Another thing with your query is else (DATEADD('day', -7, getdate())) I believe you can remove this else and add it to different day, because dayofweek() can only return 7 values, (0-6), so you can add the else for any other day, instead of when then for that day, so if my previous suggestion doesn't work, (I don't know why it shouldn't), you can simplify it with this suggestion.