求计算医院住院期初人数的sql

[TLJewel]

表结构如下
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
)
select * from tab1

注：
1.状态为 I 表示患者还未出院。
2.因为患者最早入院时9月1日，所以9月的期初为0.

期望的结果：
year_month count
201408 0
201409 2
201410 4
201411 3

大神帮帮忙，这类的sql没写过。

[小灰狼W]

统计的是每个月的第一天的0点有多少个住院人数？

[小灰狼W]

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_