病历质控统计

select B_DEPT,count(decode(单据,'病案首页',1)) as 首页信息,count(decode(单据,'入院记录',2)) as 入院记录，
count(decode(单据,'病程记录',3)) as 病程记录,count(decode(单据,'手术相关记录',4)) as 手术记录,
count(decode(单据,'出院记录',5)) as 出院记录,count(decode(单据,'出院记录',5)) as 出院记录,
count(decode(code,'A28',7,'A34',7,'A39',7,'A46',7,'A47',7,'B41',7,'C08',7,'1B03',7,'1B10',7,'1B17',7,'1B18',7,'1B23',7,
'D04',7,'D06',7,'D16',7,'1B38',7,'E08',7,'F10',7,'1B54',7,'1A59',7,'H07',7,'H17',7,'H18',7)) as 漏签字,
count(decode(单据,'知情同意书',6)) as 知情同意书,count(decode(code,'H10',8,'H12',8,'H13',8,'H15',8,'H17',8,'H18',8)) as 医嘱单,
count(decode(code,'1B58',9,'1B59',9)) as 体温单,count(decode(code,'C46',10,'C47',10,'C48',10,'C49',10,'C50',10,'C25',10,
'1B14',10,'1B15',10,'1B16',10,'1B17',10,'1B19',10)) as 会诊记录,count(decode(code,'A08',11)) as 首页主诊断,count(decode(code,'A49',12)) as 主手术操作,
count(decode(单据,'辅助检查',13)) as 辅助检查
  from (
select '病案首页' as 单据,c.title, b.id, b.code, b.title, b.cid,d.B_DEPT,d.ASSESS_TIME, b.pid,a.id as aid
  from Doc_Defect_Log a,
       Doc_QC_Rule b,
       DOC_QC_LOG d，
       DOC_QC_PLAN c
 where a.rule_id = b.id
   and a.qc_log_id = d.id
   and b.pid = c.id
   and c.disable_time is null
   and c.id = '08DE9B1AEED245B3844F621DC4412190'
   and (b.code like'A%'or b.code in ('1B79','1B80','1B81','1B82','1B83','1B84','1B85')) 
   
union all

select '入院记录' as 单据,c.title, b.id, b.code, b.title, b.cid,d.B_DEPT,d.ASSESS_TIME, b.pid,a.id as aid
  from Doc_Defect_Log a,
       Doc_QC_Rule b,
       DOC_QC_LOG d，
       DOC_QC_PLAN c
 where a.rule_id = b.id
   and a.qc_log_id = d.id
   and b.pid = c.id
   and c.disable_time is null
   and c.id = '08DE9B1AEED245B3844F621DC4412190'
   and (b.code like'B%'or b.code in ('1A66','1A67','1A68','1A69','1A90','1A91','1A92','1A93') or b.code like'1A7%' or b.code like'1A8%') 

union all

select '病程记录' as 单据,c.title, b.id, b.code, b.title, b.cid,d.B_DEPT,d.ASSESS_TIME, b.pid,a.id as aid
  from Doc_Defect_Log a,
       Doc_QC_Rule b,
       DOC_QC_LOG d，
       DOC_QC_PLAN c
 where a.rule_id = b.id
   and a.qc_log_id = d.id
   and b.pid = c.id
   and c.disable_time is null
   and c.id = '08DE9B1AEED245B3844F621DC4412190'
   and (b.code like'C%'or b.code in ('1A94','1A95','1A96','1A97','1A98','1A99','1B30')OR  b.code like'1B0%' OR b.code like'1B1%' OR b.code like'1B2%') 
     
 union all
 
 select '手术相关记录' as 单据,c.title, b.id, b.code, b.title, b.cid,d.B_DEPT,d.ASSESS_TIME, b.pid,a.id
  from Doc_Defect_Log a,
       Doc_QC_Rule b,
       DOC_QC_LOG d，
       DOC_QC_PLAN c
 where a.rule_id = b.id
   and a.qc_log_id = d.id
   and b.pid = c.id
   and c.disable_time is null
   and c.id = '08DE9B1AEED245B3844F621DC4412190'
   and (b.code like'D%'or b.code in ('1B63','1B64','1B71','1B72') or b.code like'1B3%' or b.code like'1B4%' ) 
   AND B.CODE not in 'D25'

union all

select '上级医师查房记录' as 单据,c.title, b.id, b.code, b.title, b.cid,d.B_DEPT,d.ASSESS_TIME, b.pid,a.id as aid
  from Doc_Defect_Log a,
       Doc_QC_Rule b,
       DOC_QC_LOG d，
       DOC_QC_PLAN c
 where a.rule_id = b.id
   and a.qc_log_id = d.id
   and b.pid = c.id
   and c.disable_time is null
   and c.id = '08DE9B1AEED245B3844F621DC4412190'
   and (b.code like'E%') 
   
union all 

select '出院记录' as 单据,c.title, b.id, b.code, b.title, b.cid,d.B_DEPT,d.ASSESS_TIME, b.pid,a.id as aid
  from Doc_Defect_Log a,
       Doc_QC_Rule b,
       DOC_QC_LOG d，
       DOC_QC_PLAN c
 where a.rule_id = b.id
   and a.qc_log_id = d.id
   and b.pid = c.id
   and c.disable_time is null
   and c.id = '08DE9B1AEED245B3844F621DC4412190'
   and (b.code like'F%'or b.code in ('1B50','1B51','1B52','1B53')) 

union all

select '辅助检查' as 单据,c.title, b.id, b.code, b.title, b.cid,d.B_DEPT,d.ASSESS_TIME, b.pid,a.id as aid
  from Doc_Defect_Log a,
       Doc_QC_Rule b,
       DOC_QC_LOG d，
       DOC_QC_PLAN c
 where a.rule_id = b.id
   and a.qc_log_id = d.id
   and b.pid = c.id
   and c.disable_time is null
   and c.id = '08DE9B1AEED245B3844F621DC4412190'
   and (b.code like'G%') 

union all 

select '知情同意书' as 单据,c.title, b.id, b.code, b.title, b.cid,d.B_DEPT,d.ASSESS_TIME, b.pid,a.id as aid
  from Doc_Defect_Log a,
       Doc_QC_Rule b,
       DOC_QC_LOG d，
       DOC_QC_PLAN c
 where a.rule_id = b.id
   and a.qc_log_id = d.id
   and b.pid = c.id
   and c.disable_time is null
   and c.id = '08DE9B1AEED245B3844F621DC4412190'
   and (b.code in ('1B54','1B55','1B56','1B57')) 
   
union all

select '病历书写要求' as 单据,c.title, b.id, b.code, b.title, b.cid,d.B_DEPT,d.ASSESS_TIME, b.pid,a.id as aid
  from Doc_Defect_Log a,
       Doc_QC_Rule b,
       DOC_QC_LOG d，
       DOC_QC_PLAN c
 where a.rule_id = b.id
   and a.qc_log_id = d.id
   and b.pid = c.id
   and c.disable_time is null
   and c.id = '08DE9B1AEED245B3844F621DC4412190'
   and (b.code like'Z%'or b.code in ('1A59','1A60','1A61','1A62','1A63','1A64','1A65')) 
   
UNION ALL 

select '护理记录' as 单据,c.title, b.id, b.code, b.title, b.cid,d.B_DEPT,d.ASSESS_TIME, b.pid,a.id as aid
  from Doc_Defect_Log a,
       Doc_QC_Rule b,
       DOC_QC_LOG d，
       DOC_QC_PLAN c
 where a.rule_id = b.id
   and a.qc_log_id = d.id
   and b.pid = c.id
   and c.disable_time is null
   and c.id = '08DE9B1AEED245B3844F621DC4412190'
   and ( b.code in ('1B59','1B58','1B60','1B61','1B65','1B66','1B67','1B68','1B69','1B70','D25'))   
union all
select '医嘱及病历书写' as 单据,c.title, b.id, b.code, b.title, b.cid,d.B_DEPT,d.ASSESS_TIME, b.pid,a.id as aid
  from Doc_Defect_Log a,
       Doc_QC_Rule b,
       DOC_QC_LOG d，
       DOC_QC_PLAN c
 where a.rule_id = b.id
   and a.qc_log_id = d.id
   and b.pid = c.id
   and c.disable_time is null
   and c.id = '08DE9B1AEED245B3844F621DC4412190'
   and (b.code like'H%'or b.code in ('1B73','1B74','1B75','1B76','1B77','1B78')) 
   ) 
where ASSESS_TIME >sysdate-10   
group by B_DEPT