CASE in WHERE Clause in Snowflake

Viewed 125

I am trying to do a case statement within the where clause in snowflake but I’m not quite sure how should I go about doing it.

What I’m trying to do is, if my current month is Jan, then the where clause for date is between start of previous year and today. If not, the where clause for date would be between start of current year and today.

WHERE 
CASE MONTH(CURRENT_DATE()) = 1 THEN DATE BETWEEN DATE_TRUNC(‘YEAR’, DATEADD(YEAR, -1, CURRENT_DATE())) AND CURRENT_DATE()
CASE MONTH(CURRENT_DATE()) != 1 THEN DATE BETWEEN DATE_TRUNC(‘YEAR’, CURRENT_DATE()) AND CURRENT_DATE()
END

Appreciate any help on this!

3 Answers

Use a CASE expression that returns -1 if the current month is January or 0 for any other month, so that you can get with DATEADD() a date of the previous or the current year to use in DATE_TRUNC():

WHERE DATE BETWEEN 
        DATE_TRUNC('YEAR', DATEADD(YEAR, CASE WHEN MONTH(CURRENT_DATE()) = 1 THEN -1 ELSE 0 END, CURRENT_DATE())) 
        AND 
        CURRENT_DATE()

I suspect that you don't even need to use CASE here:

WHERE
    (MONTH(CURRENT_DATE()) = 1 AND
     DATE BETWEEN DATE_TRUNC(‘YEAR’, DATEADD(YEAR, -1, CURRENT_DATE())) AND
                  CURRENT_DATE()) OR
    (MONTH(CURRENT_DATE()) != 1 AND
     DATE BETWEEN DATE_TRUNC(‘YEAR’, CURRENT_DATE()) AND CURRENT_DATE())

So the other answers are quite good, but... the answer can be even simpler

Making a little table to brake down what is happening.

select 
    row_number() over (order by null) - 1 as rn, 
    dateadd('day', rn * 5, date_trunc('year',current_date())) as pretend_current_date,
    DATEADD(YEAR, -1, pretend_current_date) as pcd_sub1,
    month(pretend_current_date) as pcd_month,
    DATE_TRUNC(year, iff(pcd_month = 1, pcd_sub1, pretend_current_date)) as _from,
    pretend_current_date as _to
from table(generator(ROWCOUNT => 30))
order by rn;

this shows:

RN PRETEND_CURRENT_DATE PCD_SUB1 PCD_MONTH _FROM _TO
0 2022-01-01 2021-01-01 1 2021-01-01 2022-01-01
1 2022-01-06 2021-01-06 1 2021-01-01 2022-01-06
2 2022-01-11 2021-01-11 1 2021-01-01 2022-01-11
3 2022-01-16 2021-01-16 1 2021-01-01 2022-01-16
4 2022-01-21 2021-01-21 1 2021-01-01 2022-01-21
5 2022-01-26 2021-01-26 1 2021-01-01 2022-01-26
6 2022-01-31 2021-01-31 1 2021-01-01 2022-01-31
7 2022-02-05 2021-02-05 2 2022-01-01 2022-02-05
8 2022-02-10 2021-02-10 2 2022-01-01 2022-02-10
9 2022-02-15 2021-02-15 2 2022-01-01 2022-02-15
10 2022-02-20 2021-02-20 2 2022-01-01 2022-02-20
11 2022-02-25 2021-02-25 2 2022-01-01 2022-02-25
12 2022-03-02 2021-03-02 3 2022-01-01 2022-03-02
13 2022-03-07 2021-03-07 3 2022-01-01 2022-03-07
14 2022-03-12 2021-03-12 3 2022-01-01 2022-03-12
15 2022-03-17 2021-03-17 3 2022-01-01 2022-03-17
16 2022-03-22 2021-03-22 3 2022-01-01 2022-03-22
17 2022-03-27 2021-03-27 3 2022-01-01 2022-03-27
18 2022-04-01 2021-04-01 4 2022-01-01 2022-04-01
19 2022-04-06 2021-04-06 4 2022-01-01 2022-04-06
20 2022-04-11 2021-04-11 4 2022-01-01 2022-04-11
21 2022-04-16 2021-04-16 4 2022-01-01 2022-04-16
22 2022-04-21 2021-04-21 4 2022-01-01 2022-04-21
23 2022-04-26 2021-04-26 4 2022-01-01 2022-04-26
24 2022-05-01 2021-05-01 5 2022-01-01 2022-05-01
25 2022-05-06 2021-05-06 5 2022-01-01 2022-05-06
26 2022-05-11 2021-05-11 5 2022-01-01 2022-05-11
27 2022-05-16 2021-05-16 5 2022-01-01 2022-05-16
28 2022-05-21 2021-05-21 5 2022-01-01 2022-05-21
29 2022-05-26 2021-05-26 5 2022-01-01 2022-05-26

Your logic is asking "is the current date in the month of January", at which point take the prior year, and then date truncate to the year, otherwise take the current date and truncate to the year. As the start of a BETWEEN test.

This is the same as getting the current date subtracting one month, and truncating this to year.

Thus there is no need for any IFF or CASE

WHERE date BETWEEN DATE_TRUNC(year, DATEADD(month,-1, CURRENT_DATE())) AND CURRENT_DATE()

and if you like to drop some paren's, CURRENT_DATE can be used if you leave it in upper case, thus it can even be smaller:

WHERE date BETWEEN DATE_TRUNC(year, DATEADD(month,-1, CURRENT_DATE)) AND CURRENT_DATE
Related