teradata-sql-assistantteradatasql

CASE WHEN in WHERE clause to filter the dates


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.


Solution

  • 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