CODEWITHSUNDEEP
sqloracle-databaseoracle11g
simplify_life
simplify_life

Reputation: 405

oracle select data between date range using connect by clause

I have data something like this

date        count
01-JAN-2015 10
02-JAN-2015 20
03-JAN-2015 30
01-FEB-2015 4
02-FEB-2015 8
03-FEB-2015 12
01-MAR-2015 5
02-MAR-2015 10
03-MAR-2015 15
01-APR-2015 6    
02-APR-2015 12
03-APR-2015 18
01-MAY-2015 7    
02-MAY-2015 14
03-MAY-2015 21
01-JUN-2015 8   
02-JUN-2015 16
03-JUN-2015 24
01-JUL-2015 8   
02-JUL-2015 16
03-JUL-2015 24

I need result group by months with variable number of months from current month

Example If I need only for next 2 months from today result is

MAR-2015  24
APR-2015  36

and If I need only for next 3 months from today result is

MAR-2015  24
APR-2015  36
MAY-2015  42

I have query to the get variable months with start date and end date of month 

SELECT TO_CHAR(TRUNC(ADD_MONTHS(sysdate,level),'MM'),'MON-yyyy') MNTH ,
  TO_CHAR(TRUNC(ADD_MONTHS(sysdate,level),'MM'),'dd-MON-yyyy') strt_date,
  TO_CHAR(TRUNC(LAST_DAY(ADD_MONTHS(SYSDATE, level))),'dd-MON-yyyy') end_date
FROM dual
  CONNECT BY LEVEL <= p_level

Where p_level is variable number of months like 2,3,4....

Can any 1 help using SQL query without using PL/SQL

Upvotes: 1

Views: 2170

Answers (1)

Alen Oblak
Alen Oblak

Reputation: 3325

You don't need to use a connect by clause at all.

select   to_char(trunc(t.date, 'mm'), 'MON-YY')
,        count(1)
from     your_table_here t
where    trunc(t.date, 'mm') > sysdate
and      trunc(t.date, 'mm') < add_months(sysdate, :months)
group by trunc(t.date, 'mm')

Just insert the correct value for :months variable.

Upvotes: 3

Related Questions