Hi,
I'm trying to split date ranges into months by using connect by clause. This approach is working if the number of rows are less.
When the rowcount is increasing, ending up with a timeout error. Could you please suggest me if there is any other approach for this requirement?
select level r,
greatest( add_months(trunc(sdate,'mm'),level-1), sdate ),
least( last_day( add_months(sdate,level-1) ), edate )
from (select to_date( :sdate, 'dd-mon-yyyy' ) sdate,
to_date( :edate, 'dd-mon-yyyy' ) edate
from dual)
connect by level <= months_between( trunc( edate,'mm'), trunc(sdate,'mm') ) + 1