case statement in a where clause-CodePudding

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

CodePudding user response：

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.

CodePudding user response：

The correction above will fix the case statement for the use with BETWEEN, but in following the desired results, I thought there may be an issue with the day of week logic - particularly if the intent was to get Monday->Today as long as it's within a week.

Of note, check your start of week setting, as it may not return a 0 here for start of week.