CASE in WHERE Clause in Snowflake-CodePudding

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!

CodePudding user response：

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())

CodePudding user response：

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()