CASE WHEN in WHERE clause to filter the dates

Question

I'm super junior in SQL and trying to automate my date parameters in below the codes in Teradata:

SELECT
POST_DATE_YR,
POST_DATE_MN,
SERV_NAME,
MARKET_NAME,
COUNTRY_NAME

FROM
MY_TABLE

WHERE

CASE WHEN (EXTRACT(MONTH FROM CURRENT_DATE)+6)<=12
     THEN (POST_DATE_YR = '2022' AND POST_DATE_MN Between EXTRACT(MONTH FROM CURRENT_DATE) AND EXTRACT(MONTH FROM CURRENT_DATE)+6 )

     ELSE 
          (POST_DATE_YR = '2022' AND POST_DATE_MN Between EXTRACT(MONTH FROM CURRENT_DATE) AND 12 )
          or
          (POST_DATE_YR = '2023' AND POST_DATE_MN Between 1 and EXTRACT(MONTH FROM CURRENT_DATE)-6 )

END

ORDER BY 1,2,3,4,5

What I'm trying to define is: If current_month+6 <=12, then define date parameters as year=2022 and month between current_month and current_month+6.

If current_month+6 >12, then define date parameters as year=2022 and month between current_month and 12 PLUS year=2023 and month between 1 and current_month-6

It would be always 7-month data.

But I got error when executing. Can someone please help on how to achieve this? Thanks.

*I got error* is a useless problem description. What error did you get? What's the exact, complete error message? It's on the screen right in front of you, but we can't see that screen from here. That error message contains useful information, which you should be sharing with us so that we can use it to help you. — Ken White, May 04 '22 at 19:48
It returns Syntax error: expected something between the word 'POST_DATE_YR' and '=' — Winniethewinner, May 04 '22 at 20:58

score 0 · Accepted Answer · answered May 04 '22 at 19:52

The CASE expression returns a value; the WHERE clause requires one or more predicates (conditions), not values.

SELECT
 POST_DATE_YR,
 POST_DATE_MN,
 SERV_NAME,
 MARKET_NAME,
 COUNTRY_NAME
FROM
 MY_TABLE
WHERE
(
  EXTRACT(MONTH FROM CURRENT_DATE)+6)<=12
  AND POST_DATE_YR = '2022' 
  AND POST_DATE_MN Between EXTRACT(MONTH FROM CURRENT_DATE) 
  AND EXTRACT(MONTH FROM CURRENT_DATE)
)
OR
(
  POST_DATE_YR = '2022' 
  AND POST_DATE_MN Between EXTRACT(MONTH FROM CURRENT_DATE) AND 12 
)
OR
(
  POST_DATE_YR = '2023' 
  AND POST_DATE_MN Between 1 and EXTRACT(MONTH FROM CURRENT_DATE)-6 
)
ORDER BY 1,2,3,4,5

CASE WHEN in WHERE clause to filter the dates

1 Answers1