Where clause using case statement

Database
Enthusiast

Where clause using case statement

I am trying to write a query in which I need to assign a filter based on day of the week

     Select * from TableA

     Where Revenue_Month between Month-13 and month-1

I want to pull the data for 13 months history. For example,the user  request comes on 3rd Jan 2015. So Data from Nov 2013 to Nov 2014 will be extracted (both months included ).If the request comes on 25th Jan 2015. Then, Data from Dec 2013 to Dec 2014 will be provided (both months included).

I want to make this dynamic so if it is before 5th of every month the where clause should change to Revenue_Month between substr(add_months(date,-14) (format 'yyyymm'),1,7) and substr(add_months(date,-2) (format 'yyyymm'),1,7)

I am unable write a case statement within the Where clause to get the logic I need.I tried to take a swing at it but it gives me syntax errors. The query I use is mentioned below.

Select    * FROM   TABLEA  

Where

       Case when (select day_of_week    from sys_calendar.calendar   where

     calendar_date = date)  >=5 then TableA.Revenue_Month  between  substr(add_months(date,-13) (format 'yyyymm'),1,7) and substr(add_months(date,-1) (format 'yyyymm'),1,7)

        else    REVN_CAL_MO.MO  between substr(add_months(date,-14) (format 'yyyymm'),1,7) and substr(add_months(date,-2) (format 'yyyymm'),1,7)

        end

8 REPLIES
Senior Apprentice

Re: Where clause using case statement

REVN_CAL_MO.MO is a CHAR?

Simply substract 5 days from today and use this date instead:

REVN_CAL_MO.MO between trim(add_months(date - 5,-14) (format 'yyyymm')) 
and trim(add_months(date - 5, -2) (format 'yyyymm'))
Enthusiast

Re: Where clause using case statement

Thanks Dieter!!!

But my requirement is when i request the data on or before 5th of any month it should pick me 13 months of data starting from second last month and going in reverse chronological manner.For example request comes on 4th Feb 2015. So Data from Dec 2013 to Dec 2014 will be extracted (both months included ).

When i request data after 5th of any month then it should pick 13 months of data starting from last month and going in reverse chronological manner.For example, request comes on 9th Feb 2015. Then, Data from Jan 2014 to Jan 2015 will be provided (both months included).

So I am trying to achieve this requirement in the where clause using the CASE statement. But it is giving syntax error.  Below is the where clause:

Select    * FROM   TABLEA  

Where

       Case when (select extract(day from date))  >=5 then TableA.Revenue_Month  between  substr(add_months(date,-13) (format 'yyyymm'),1,7) and substr(add_months(date,-1) (format 'yyyymm'),1,7)

        else    REVN_CAL_MO.MO  between substr(add_months(date,-14) (format 'yyyymm'),1,7) and substr(add_months(date,-2) (format 'yyyymm'),1,7)

        end.

Please help me in correcting the above query.

Thanks in advance!!!

Regards,

Rekha

Enthusiast

Re: Where clause using case statement

Hi,

Can you please share the data stored in REVN_CAL_MO.MO  and Revenue_Month and even datatype.

Thanks,

Rohan Sawant

Enthusiast

Re: Where clause using case statement

The data type of Revenue_Month is integer.

Select    * FROM   TABLEA  

Where

       Case when (select extract(day from date))  >=5 then TableA.Revenue_Month  between  substr(add_months(date,-13) (format 'yyyymm'),1,7) and substr(add_months(date,-1) (format 'yyyymm'),1,7)

        else    TableA.Revenue_Month   between substr(add_months(date,-14) (format 'yyyymm'),1,7) and substr(add_months(date,-2) (format 'yyyymm'),1,7)

        end.

Regards,

Rekha

Enthusiast

Re: Where clause using case statement

Sample data for REVENUE_MONTH:201501, 210502,.... (basically year month data in the format YYYYMM)

Regards,

Rekha

Enthusiast

Re: Where clause using case statement

Hi,

I didnt go into the logic much. Just tried to run your query.

SELECT    
*
FROM
TABLEA
WHERE
REVENUE_MONTH
BETWEEN
CASE
WHEN (SELECT EXTRACT(DAY FROM DATE)) >=5
THEN SUBSTR(ADD_MONTHS(DATE,-13) (FORMAT 'YYYYMM'),1,7)
ELSE SUBSTR(ADD_MONTHS(DATE,-14) (FORMAT 'YYYYMM'),1,7)
END
AND
CASE
WHEN (SELECT EXTRACT(DAY FROM DATE)) >=5
THEN SUBSTR(ADD_MONTHS(DATE,-1) (FORMAT 'YYYYMM'),1,7)
ELSE SUBSTR(ADD_MONTHS(DATE,-2) (FORMAT 'YYYYMM'),1,7)
END;

 (Just a Suggestion : Use the CASE logic in SELECT give a alias and then use in WHERE clause for performance).

Thanks,

Rohan Sawant

Enthusiast

Re: Where clause using case statement

Thanks Rohan!!!! The solution works :)

N/A

Re: Where clause using case statement

Hi,

I think below condition will work.

REVN_CAL_MO.MO between trim(add_months(date - 5,-13) (format 'yyyymm'))
and trim(add_months(date - 5, -1) (format 'yyyymm'))