病历24小时完成率

select '入院记录' as 单据类型,wcd as 完成数,zj as 单据总数,wcd/zj as 完成率  from（
select count(b.id) wcd from(
select a.id,b.create_time,b.complete_time from BZ_ACT_LOG a,bz_doc_tasks b, bz_doc_log c where a.id = b.actlog_id  and b.real_doc_id =c.id and c.creat_time >sysdate -30 
and c.title like'入院记录' and b.complete_time is not null) b  where  (b.complete_time-b.create_time)*24<24 ) a,(
select count (a.id) zj from 
(select a.id,b.create_time,b.complete_time from BZ_ACT_LOG a,bz_doc_tasks b, bz_doc_log c where a.id = b.actlog_id  and b.real_doc_id =c.id and c.creat_time >sysdate -30 
and c.title like'入院记录') a)
union all
select '手术记录' as 单据类型,wcd as 完成数,zj as 单据总数,wcd/zj as 完成率  from（
select count(b.id) wcd from(
select a.id,b.create_time,b.complete_time from BZ_ACT_LOG a,bz_doc_tasks b, bz_doc_log c where a.id = b.actlog_id  and b.real_doc_id =c.id and c.creat_time >sysdate -30 
and c.title like'手术记录' and b.complete_time is not null) b  where  (b.complete_time-b.create_time)*24<24 ) a,(
select count (a.id) zj from 
(select a.id,b.create_time,b.complete_time from BZ_ACT_LOG a,bz_doc_tasks b, bz_doc_log c where a.id = b.actlog_id  and b.real_doc_id =c.id and c.creat_time >sysdate -30 
and c.title like'手术记录') a)
union all
select '出院记录' as 单据类型,wcd as 完成数,zj as 单据总数,wcd/zj as 完成率  from（
select count(b.id) wcd from(
select a.id,b.create_time,b.complete_time from BZ_ACT_LOG a,bz_doc_tasks b, bz_doc_log c where a.id = b.actlog_id  and b.real_doc_id =c.id and c.creat_time >sysdate -30 
and c.title like'出院记录' and b.complete_time is not null) b  where  (b.complete_time-b.create_time)*24<24 ) a,(
select count (a.id) zj from 
(select a.id,b.create_time,b.complete_time from BZ_ACT_LOG a,bz_doc_tasks b, bz_doc_log c where a.id = b.actlog_id  and b.real_doc_id =c.id and c.creat_time >sysdate -30 
and c.title like'出院记录') a)
union all 
select '抢救记录' as 单据类型,wcd as 完成数,zj as 单据总数,wcd/zj as 完成率  from（
select count(a.id) wcd  from BZ_ACT_LOG a,(select actlog_id,real_doc_id,病历,
       create_time,
       complete_time from (select t.actlog_id,t.real_doc_id,decode(instr(t.contenttext, '抢救记录'),
              0,
              null,
              null,null,
              '抢救记录') as 病历,
       t.create_time,
       t.complete_time
  from bz_doc_tasks t  ) where create_time  >sysdate -30 and 病历 is not null and complete_time is not null) b
  where a.id = b.actlog_id  and  (b.complete_time-b.create_time)*24<6) a,(
  select count(a.id) Zj  from BZ_ACT_LOG a,(select actlog_id,real_doc_id,病历,
       create_time,
       complete_time from (select t.actlog_id,t.real_doc_id,decode(instr(t.contenttext, '抢救记录'),
              0,
              null,
              null,null,
              '抢救记录') as 病历,
       t.create_time,
       t.complete_time
  from bz_doc_tasks t  ) where create_time  >sysdate -30 and 病历 is not null) b
  where a.id = b.actlog_id  )b 
union all
select '病案首页' as 单据类型,wcd as 完成数,zj as 单据总数,wcd/zj as 完成率  from
（
select count( a.病人id||a.主页id||title||a.create_time||complete_time) wcd from( 
select a.病人id,a.主页id,a.出院日期,a.id,t.complete_time,t.create_time,d.title from bz_doc_tasks t ,bz_doc_log d,
（select b.病人id,b.主页id, b.出院日期, c.id
  from 病人变动记录@zlhisdb a, 病案主页@zlhisdb b, BZ_ACT_LOG c
 where a.病人id = b.病人id
   and a.主页id = b.主页id
   and substr(extend_tag, 4) = a.id ) a
   where   t.actlog_id =a.id
   and t.real_doc_id = d.id
   and t.complete_time is not null
   and a.出院日期 >sysdate-30  and (t.complete_time-t.create_time)*24<24) a)  
   a,(   
select count( a.病人id||a.主页id||title||a.create_time||complete_time) zj from( 
select a.病人id,a.主页id,a.出院日期,a.id,t.complete_time,t.create_time,d.title from bz_doc_tasks t ,bz_doc_log d,（select b.病人id,b.主页id, b.出院日期, c.id
  from 病人变动记录@zlhisdb a, 病案主页@zlhisdb b, BZ_ACT_LOG c
 where a.病人id = b.病人id
   and a.主页id = b.主页id
   and substr(extend_tag, 4) = a.id )a
   where   t.actlog_id =a.id
   and t.real_doc_id = d.id
   and a.出院日期 >sysdate-30 ) a)b