CASE WHEN statement

Database
Sir
N/A

CASE WHEN statement

I need to write a case statement in the WHERE clause, which is -
when current_date is 1st of Month then select data BETWEEN 1st day of prev month AND last day prev month
ELSE FROM 1st of Curr month till date. I have written this so far but it is not working. '05/01/2017' will be input date

	SELECT *
	FROM	MyTable
	WHERE calendar_date
		BETWEEN
			CASE WHEN extract (day from CAST( '05/01/2017' AS DATE FORMAT 'MM/DD/YYYY')) =1 --check to see date is 1st of month
			THEN  ADD_MONTHS((CAST( '05/01/2017' AS DATE FORMAT 'MM/DD/YYYY') - EXTRACT(DAY FROM CAST( '05/01/2017' AS DATE FORMAT 'MM/DD/YYYY'))+1), -1) --1st of prev month
		AND ADD_MONTHS(CURRENT_DATE - EXTRACT(DAY FROM CURRENT_date), 0 ) --last day prev month
		ELSE        CAST( '05/01/2017' AS DATE FORMAT 'MM/DD/YYYY') - EXTRACT(DAY FROM CAST( '05/01/2017' AS DATE FORMAT 'MM/DD/YYYY'))+1 --else 1st of Curr montH
	AND CURRENT_DATE
	END
	ORDER BY calendar_date

Please guide

 

1 REPLY
Teradata Employee

Re: CASE WHEN statement

Use two case statements, one to compute the lower bound of the BETWEEN and one for the upper bound.

 

BETWEEN CASE ... END AND CASE ... END