getting monthly data for specific months based on SYSDATE

0
votes

I am running a query on a monthly basis to select data on the basis of months JAN,FEB,MAR,APR,SEP OCT,NOV,DEC.

Lets say i am running this query in FEB 2018, then the data of JAN and FEB should come from current year(2018) and data from MAR,APR,SEP OCT,NOV,DEC should come from previous year(2017). How do i handle this with SYSDATE.

>SELECT * FROM MY_TABLE WHERE MONTH_NAME 
IN ('JAN','FEB','MAR','APR','SEP','OCT','NOV','DEC') 
AND 
YEAR=TO_CHAR(TRUNC(SYSDATE),'YYYY');

Assuming i have columns MONTH_NAME having all month names and YEAR with year to match the record.

2
oracleoracle11goracle10g
Please, tell the rules in what cases you need data only from current year, and when from previous and current. - Eduard Okhvat
Also add some sample data and expected results. - Kaushik Nayak

2 Answers

2
votes

Without sample data it's hard to tell what exactly you want, but it could be something like this.

SELECT * 
  FROM   my_table 
WHERE  month_name IN ( 'JAN', 'FEB', 'MAR', 'APR', 
                       'SEP', 'OCT', 'NOV', 'DEC' ) 

AND ( 
      ( EXTRACT ( MONTH FROM TO_DATE( month_name ,'MON' ) ) 
                    <= EXTRACT ( MONTH FROM SYSDATE ) AND YEAR = 2018) OR
      ( EXTRACT ( MONTH FROM TO_DATE( month_name ,'MON' ) ) 
                  >  EXTRACT ( MONTH FROM SYSDATE ) AND YEAR = 2017)
    )

Preferably add 'NLS_DATE_LANGUAGE = ENGLISH' as 3rd argument of TO_DATE

0
votes

Without a sample data I'd suggest the following approach:

  • obtain the list of required dates
select  add_months(
            trunc(sysdate,'mm'),
            - level + 1
        )
  from  dual
connect by level <= 12;

this query will return first days of last 11 months and the currenct month

  • then join the results of this query to your table