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.

Answer 2

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. https://docs.snowflake.com/en/sql-reference/functions-date-time.html#first-day-of-the-week

I created a test case, and tested it - it appears to work per the the desired logic described above:

create
or replace table testdata (
    create_date date,
    c2 varchar,
    c1 int default dayofweekiso(create_date)
);
insert into
    testdata (create_date, c2)
values
    (to_date('07-10-2022', 'mm-dd-yyyy'),'Sunday'),
    (to_date('07-11-2022', 'mm-dd-yyyy'),'Monday'),
    (to_date('07-12-2022', 'mm-dd-yyyy'), 'Tuesday'),
    (to_date('07-13-2022', 'mm-dd-yyyy'), 'Wednesday'),
    (to_date('07-14-2022', 'mm-dd-yyyy'),'Thursday'),
    (to_date('07-15-2022', 'mm-dd-yyyy'),'Friday');

select
    current_date(),
    dayofweek(current_date()),
    dayofweekiso(current_date),
    create_date
from
    testdata
where
    (
        dayofweekiso(create_date) = dayofweekiso(current_date())
        and create_date = current_date()
    )
    or create_date between case
        dayofweekiso(current_date())
        when 2 then (DATEADD('day', -1, current_date()))
        when 3 then (DATEADD('day', -2, current_date()))
        when 4 then (DATEADD('day', -3, current_date()))
        when 5 then (DATEADD('day', -4, current_date()))
        when 6 then (DATEADD('day', -5, current_date()))
        when 7 then (DATEADD('day', -6, current_date()))
    end
    and current_date();

It's only Tuesday, I expect to see just Monday and Tuesday's data in the results:

CURRENT_DATE()	DAYOFWEEK(CURRENT_DATE())	DAYOFWEEKISO(CURRENT_DATE)	CREATE_DATE
2022-07-12	2	2	2022-07-11
2022-07-12	2	2	2022-07-12

Answer 3

The first point to make is you case statement is truncating to the start of the week, so you should just do DATE_TRUNC

This code gets some days, and show with them self join how the days are in or out which is show how your current_date() is the same as d1

with data(date) as (
    select 
        to_date(column1, 'mm-dd-yyyy')
    from values
        ('07-10-2022'),
        ('07-11-2022'),
        ('07-12-2022'),
        ('07-13-2022'),
        ('07-14-2022'),
        ('07-15-2022')
)
select d1.date as d1
    ,d2.date as d2
    ,dayname(d1) as day_name
    ,DAYOFWEEK(d1) as day_of_week
    ,date_trunc('week', d1) as trunc_d1
    ,d2 between trunc_d1 and d1 as in_range
from data as d1
cross join data as d2
order by 1;

D1	D2	DAY_NAME	DAY_OF_WEEK	TRUNC_D1	IN_RANGE
2022-07-10	2022-07-11	Sun	0	2022-07-04	FALSE
2022-07-10	2022-07-12	Sun	0	2022-07-04	FALSE
2022-07-10	2022-07-13	Sun	0	2022-07-04	FALSE
2022-07-10	2022-07-14	Sun	0	2022-07-04	FALSE
2022-07-10	2022-07-15	Sun	0	2022-07-04	FALSE
2022-07-10	2022-07-10	Sun	0	2022-07-04	TRUE
2022-07-11	2022-07-14	Mon	1	2022-07-11	FALSE
2022-07-11	2022-07-15	Mon	1	2022-07-11	FALSE
2022-07-11	2022-07-13	Mon	1	2022-07-11	FALSE
2022-07-11	2022-07-12	Mon	1	2022-07-11	FALSE
2022-07-11	2022-07-11	Mon	1	2022-07-11	TRUE
2022-07-11	2022-07-10	Mon	1	2022-07-11	FALSE
2022-07-12	2022-07-10	Tue	2	2022-07-11	FALSE
2022-07-12	2022-07-11	Tue	2	2022-07-11	TRUE
2022-07-12	2022-07-12	Tue	2	2022-07-11	TRUE
2022-07-12	2022-07-13	Tue	2	2022-07-11	FALSE
2022-07-12	2022-07-14	Tue	2	2022-07-11	FALSE
2022-07-12	2022-07-15	Tue	2	2022-07-11	FALSE
2022-07-13	2022-07-10	Wed	3	2022-07-11	FALSE
2022-07-13	2022-07-15	Wed	3	2022-07-11	FALSE
2022-07-13	2022-07-14	Wed	3	2022-07-11	FALSE
2022-07-13	2022-07-13	Wed	3	2022-07-11	TRUE
2022-07-13	2022-07-12	Wed	3	2022-07-11	TRUE
2022-07-13	2022-07-11	Wed	3	2022-07-11	TRUE
2022-07-14	2022-07-10	Thu	4	2022-07-11	FALSE
2022-07-14	2022-07-11	Thu	4	2022-07-11	TRUE
2022-07-14	2022-07-12	Thu	4	2022-07-11	TRUE
2022-07-14	2022-07-13	Thu	4	2022-07-11	TRUE
2022-07-14	2022-07-14	Thu	4	2022-07-11	TRUE
2022-07-14	2022-07-15	Thu	4	2022-07-11	FALSE
2022-07-15	2022-07-10	Fri	5	2022-07-11	FALSE
2022-07-15	2022-07-11	Fri	5	2022-07-11	TRUE
2022-07-15	2022-07-12	Fri	5	2022-07-11	TRUE
2022-07-15	2022-07-13	Fri	5	2022-07-11	TRUE
2022-07-15	2022-07-14	Fri	5	2022-07-11	TRUE
2022-07-15	2022-07-15	Fri	5	2022-07-11	TRUE

Thus your SQL could be written:

select 
    uuid,
    acquisition_campaign,
    REFERRAL_PROMO,
    channel,
    partner,
    created_at::date as created_date
from CONSUMER_TMP
where created_date between case date_trunc('week',getdate()) and getdate()

This is using your system start of the week default, which by default is Sunday, now if you want Monday, you could have used DAYOFWEEKISO, but there is not equivalent for DATE_TRUNC (aka 'weekiso' does not exist)