表结构如下
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没写过。
------解决思路----------------------
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_