统计报表 | 小鲜肉.Me

急性脑卒中统计报表 #

select 
      time,count(patientid) 卒中总人次 ,
   CASE WHEN count(ct_time)>0 then SUM(CASE WHEN ct_time <30 THEN 1 ELSE 0 END)/count(ct_time)*100 else 100 end  as CT30分钟达标率,
   sum(dnt_time)/count(dnt_time) DNT月平均时间,
   count(dnt_time) 溶栓例数,
   SUM(CASE WHEN dnt_time <60 THEN 1 ELSE 0 END) AS DNT小于60数,
   CASE WHEN count(dnt_time)>0 then SUM(CASE WHEN dnt_time <60 THEN 1 ELSE 0 END)/count(dnt_time)*100 else 100 end DNT小于60数达标率,
   count(jr) as STEMI介入人次
  
from (
select p.patientid,
 TO_CHAR(to_date(REPLACE(p.INADMITTIME, 'T', ' '), 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm') time,
  (to_date(ct.recording_time,'yyyy-mm-dd hh24:mi:ss')-to_date(p.INADMITTIME,'yyyy-mm-dd hh24:mi:ss'))*24*60 ct_time,
  (to_date(dnt.recording_time,'yyyy-mm-dd hh24:mi:ss')-to_date(p.INADMITTIME,'yyyy-mm-dd hh24:mi:ss'))*24*60 dnt_time,
    jr.WHEREABOUTS jr 
 from Yy_Ems_Patient p 
 left join(
      WITH RankedResults AS (
      SELECT 
        id, 
        patientid, 
        recording_time, 
        measuresing,
        ROW_NUMBER() OVER (PARTITION BY patientid ORDER BY RECORDING_TIME) AS row_num
      FROM YY_EMS_CNR
      WHERE measuresing LIKE '%CT返回%'
    )
    SELECT id, patientid, RECORDING_TIME, measuresing
    FROM RankedResults
    WHERE row_num = 1
 ) ct on ct.patientid=p.patientid
 left join (
 WITH RankedResults AS (
  SELECT 
    id, 
    patientid, 
    recording_time, 
    measuresing,
    ROW_NUMBER() OVER (PARTITION BY patientid ORDER BY RECORDING_TIME) AS row_num
  FROM YY_EMS_CNR
  WHERE measuresing LIKE '%静脉溶栓开始%'
)
SELECT id, patientid, RECORDING_TIME, measuresing
FROM RankedResults
WHERE row_num = 1
 )dnt  on dnt.patientid=p.patientid  
 left join yy_ems_dischargeassessment jr on jr.patientid=p.patientid and WHEREABOUTS='4'
 
  where p.EMERGENCYCENTER='3'  
) group by time 
order by time

卒中总例数：当月预检特殊标志为卒中的病人总数
30分钟内头颅CT完成率：分诊到ct返回时间<30分钟 / 卒中总例数 * 100% （ct返回时间取卒中抢救措施勾选ct返回的时间）
45分钟内血常规完成率：暂不统计
月平均DNT：溶栓病人的分诊时间到溶栓开始时间的总数 / 卒中急诊溶栓例数（卒中抢救措施勾选静脉溶栓算溶栓病人）
卒中急诊溶栓例数：抢救措施卒中勾选了溶栓算一例溶栓病人。
DNT≤60分钟的静脉溶栓例数：溶栓病人的分诊时间到溶栓开始时间<60分钟算一例（溶栓开始时间取勾选静脉溶栓开始的时间）
60分钟内溶栓率：DNT≤60分钟的静脉溶栓例数 / 卒中急诊溶栓例数 * 100%
急诊DSA例数：最终去向为介入室算一例

急性胸痛急诊统计表 #

select  time,count(patientid) 胸痛总人次 ,
sum(xdt_time)/count(xdt_time) 心电图月平均时间，
SUM(CASE WHEN xdt_time <10 THEN 1 ELSE 0 END) AS 心电图小于10数,
CASE WHEN count(xdt_time)>0 then SUM(CASE WHEN xdt_time <10 THEN 1 ELSE 0 END)/count(xdt_time)*100 else 100 end 心电图达标率,
  
sum(tnl_time)/count(tnl_time) TNL月平均时间,
SUM(CASE WHEN tnl_time <20 THEN 1 ELSE 0 END) ASTNL小于20数,
CASE WHEN count(tnl_time)>0 then SUM(CASE WHEN tnl_time <20 THEN 1 ELSE 0 END)/count(tnl_time)*100 else 100 end TNL达标率,
count(jr) as STEMI介入人次,
sum(my_time)/count(my_time) STEMI门药月平均时间,
SUM(CASE WHEN my_time <30 THEN 1 ELSE 0 END) AS STEMI门药小于30数,
CASE WHEN count(my_time)>0 then SUM(CASE WHEN my_time <30 THEN 1 ELSE 0 END)/count(my_time)*100 else 100 end STEMI门药达标率
  

from (
  select p.patientid,
  TO_CHAR(to_date(REPLACE(p.INADMITTIME, 'T', ' '), 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm') time,
  (to_date(xdt.sure_ecg_time,'yyyy-mm-dd hh24:mi:ss')-to_date(p.INADMITTIME,'yyyy-mm-dd hh24:mi:ss'))*24*60 xdt_time,

  (to_date(tnl.INFORMED_SIGN_TIME,'yyyy-mm-dd hh24:mi:ss')-to_date(tnl.TALK_START_TIME,'yyyy-mm-dd hh24:mi:ss'))*24*60 tnl_time,
  jr.WHEREABOUTS jr ,
  jzrs.jzrs,
  (to_date(mysj.TBYS_START_TIME,'yyyy-mm-dd hh24:mi:ss')-to_date(p.INADMITTIME,'yyyy-mm-dd hh24:mi:ss'))*24*60 my_time

   from Yy_Ems_Patient p 

  left join YY_SPECIALIZED_CPOTHER xdt on xdt.patientid=p.patientid
  left join YY_SPECIALIZED_TNL tnl on tnl.patientid=p.patientid
  left join yy_ems_dischargeassessment jr on jr.patientid=p.patientid and WHEREABOUTS='4'
  left join (
           
            SELECT 
               
              patientid, case when count(1)>0 then 1 else null end jzrs
            FROM YY_EMS_CNR
            WHERE measuresing LIKE '%急诊溶栓%' group by patientid


  ) jzrs on jzrs.patientid = p.patientid

  left join  YY_EMS_ERPCI mysj on mysj.patientid=p.patientid 


   where p.EMERGENCYCENTER='1'
) group by time 
order by time

急性胸痛患者总人次：当月预检特殊标志为胸痛的病人总数

    select * from Yy_Ems_Patient where EMERGENCYCENTER='1' --胸痛人数

首次医疗接触到首份心电图月平均时间(分)：当月所有胸痛病人的分诊到首份心电图时间之和 / 当月所有胸痛病人（首份心电图取专科表单心电图时间）
```
    select SURE_ECG_TIME from YY_SPECIALIZED_CPOTHER  --首次医疗接触到首份心电图
```
首次医疗接触到首份心电图完成时间10min内例数：分诊到首份心电图的时间间隔<10min人数
首次医疗接触到首份心电图完成时间达标率：分诊到首份心电图的时间间隔<10min人数 / 当月总胸痛人数 * 100%

TNI检测月平均时间：当月所有胸痛病人TNI时间 / 当月胸痛总人数 (TNI时间=TNI报告时间-TNI抽血时间)

    --TNL检测时间  报告时间，抽血时间
    select patientid,INFORMED_SIGN_TIME ,TALK_START_TIME from YY_SPECIALIZED_TNL

TNI检测20min内完成例数：TNI时间小于20min总人数
TNI检测时间达标率：TNI时间小于20min总人数 / 当月总胸痛人数 * 100%

STEMI急诊介入治疗人次：当月胸痛病人中最终去向为介入室人数

-- STEMI急诊介入时间  :最终去向 介入室 WHEREABOUTS =4 yy_ems_dischargeassessment   
select patientid, WHEREABOUTS from yy_ems_dischargeassessment where  WHEREABOUTS='4'

STEMI门球月平均时间(分)：不统计
STEMI门球时间90min内例数：不统计
STEMI门球时间达标率：不统计

STEMI溶栓治疗人次：胸痛抢救措施勾选急诊溶栓算一例

-- STEMI 溶栓治疗人次
SELECT 
    id, 
    patientid, 
    recording_time, 
    measuresing,
    ROW_NUMBER() OVER (PARTITION BY patientid ORDER BY RECORDING_TIME) AS row_num
FROM YY_EMS_CNR
WHERE measuresing LIKE '%急诊溶栓%'

STEMI门药月平均时间(分)：门药时间 = 分诊到溶栓的时间（溶栓时间取专科表单中溶栓开始时间）门药月平均时间 = 溶栓治疗人次的门药总时间 / 溶栓治疗人次
```
-- STEMI 门药时间    EMS_ERPCI TBYS_START_TIME
select patientid,TBYS_START_TIME from  YY_EMS_ERPCI
```
STEMI门药时间30min内例数：STEMI门药时间(分)<30分钟人数
STEMI门药时间达标率：STEMI门药时间30min内例数 / 溶栓治疗人次 * 100%