时间重叠统计

Question

有一数据表：床位,日期，变动（1增加，-1减少)当有床位增加或减少的时候 向表中添加一条记录现在要统计一段时间（每个月）内可用床位的天数例如：1号床1月1号份添加，向表中添加一条变动为1 的记录，到2月15号撤销添加一条变动为负的记录       2号床2月15号添加 ，到2月17号撤销，2月20号再添加 ，然后一直使用
那么2月份的统计结果为： 1号床2月1号到2月15号15天+2号床2月15号到17号2天+2号床2月20到28号8天=25天求查询语句，是不是有什么公式的…

Answer 1

第一直觉的写法SQL> select * from a;NO         TDATE           CHG
---------- -------- ----------
a          20110101          1
a          20110215         -1
b          20110215          1
b          20110217         -1
b          20110220          1with t as
 (select no,
         lead(tdate,
              1,
              to_char(last_day(to_Date(tdate, 'yyyymmdd')), 'yyyymmdd')) over(partition by no order by tdate) ad,
         lag(tdate, 1) over(partition by no order by tdate) bd,
         tdate,
         chg
    from a
   where substr(tdate, 1, 6) = '201102')
select sum(decode(chg,
                  1,
                  substr(ad, -2) - substr(tdate, -2),
                  (decode(bd, null, substr(tdate, -2), 0)))) total
  from t     TOTAL
----------
        25

Answer 2

我用一楼方法这样做基本达到要求了
还有没有更给力的方法：select 病区id,床号,
     sum((case  when 开始时间<to_date('2011-04-01','yyyy-mm-dd') then to_date('2011-04-01','yyyy-mm-dd')
            else 开始时间 end-a)* -1) 天数
from(
select t.病区id,t.床号,t.日期 开始时间,lead(t.日期,1,to_date('2011-04-30','yyyy-mm-dd'))over(partition by 床号,病区id order by 日期) a,变动
from 床位增减记录 t
where t.病区id=53 and t.床号 in(37,40,39,38))
where 变动=1 and 开始时间<to_date('2011-04-30','yyyy-mm-dd') and a>to_date('2011-04-01','yyyy-mm-dd')
group by 病区id,床号