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.
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