with tab1 as( select 1 id,'O' state,to_date('2014-9-1','yyyy-MM-dd') in_date,to_date('2014-11-1','yyyy-MM-dd') out_date from dual union all select 2,'O' state,to_date('2014-9-3','yyyy-MM-dd'),to_date('2014-10-23','yyyy-MM-dd') from dual union all select 3,'O' state,to_date('2014-11-1','yyyy-MM-dd') ,to_date('2014-11-26','yyyy-MM-dd') from dual union all select 4,'I' state, to_date('2014-8-1','yyyy-MM-dd'),to_date('0001-1-1','yyyy-MM-dd') from dual union all select 5,'I' state, to_date('2014-8-1','yyyy-MM-dd'),to_date('0002-1-1','yyyy-MM-dd') from dual union all select 6,'O' state, to_date('2014-10-1','yyyy-MM-dd'),to_date('2014-10-28','yyyy-MM-dd') from dual ),ym as( select add_months(to_date('201408','YYYYMM'),rownum-1) month_ from dual connect by rownum<=4) select to_char(ym.month_, 'YYYYMM') month_, count(t.id) from tab1 t right join ym on t.in_date < trunc(ym.month_, 'MM') and (t.out_date >= trunc(ym.month_, 'MM') or t.state = 'I' and sysdate >= trunc(ym.month_, 'MM')) group by ym.month_ order by ym.month_
select 1 id,'O' state,to_date('2014-9-1','yyyy-MM-dd') in_date,to_date('2014-11-1','yyyy-MM-dd') out_date from dual
union all select 2,'O' state,to_date('2014-9-3','yyyy-MM-dd'),to_date('2014-10-23','yyyy-MM-dd') from dual
union all select 3,'O' state,to_date('2014-11-1','yyyy-MM-dd') ,to_date('2014-11-26','yyyy-MM-dd') from dual
union all select 4,'I' state, to_date('2014-8-1','yyyy-MM-dd'),to_date('0001-1-1','yyyy-MM-dd') from dual
union all select 5,'I' state, to_date('2014-8-1','yyyy-MM-dd'),to_date('0002-1-1','yyyy-MM-dd') from dual
union all select 6,'O' state, to_date('2014-10-1','yyyy-MM-dd'),to_date('2014-10-28','yyyy-MM-dd') from dual
),ym as(
select add_months(to_date('201408','YYYYMM'),rownum-1) month_ from dual connect by rownum<=4)
select to_char(ym.month_, 'YYYYMM') month_, count(t.id)
from tab1 t
right join ym
on t.in_date < trunc(ym.month_, 'MM')
and (t.out_date >= trunc(ym.month_, 'MM') or
t.state = 'I' and sysdate >= trunc(ym.month_, 'MM'))
group by ym.month_
order by ym.month_