机构服务统计sql
浏览数：29 /
时间：2015年06月12日
1 SELECT UO.ORG_NAME ORGNAME, A.QYS, B.LNTJS, C.XJDAS, D.DASYS, E.GXYSFS, F.TNBSFS,G.HGDAS, H.GXYGLS, I.GXYGFS, J.TNBGLS, K.TNBGFS 2 FROM UMS_ORG UO 3 LEFT JOIN (/*新增家医服务签约*/ 4 SELECT EB.MNG_ORG_CODE ORG_CODE, COUNT(DISTINCT EB.EHR_ID) QYS 5 FROM FDS_CONTRACT FC, EHR_BASE EB 6 WHERE FC.SIGN_FAMILY_ID = EB.FAMILY_ID 7 AND FC.CONTRACT_STATE = ‘2‘ 8 AND FC.SIGN_DATE >= TO_DATE(‘2015-01-01‘,‘YYYY-MM-DD‘) 9 AND FC.SIGN_DATE <= TO_DATE(‘2015-03-30‘,‘YYYY-MM-DD‘) 10 AND EB.MNG_ORG_CODE LIKE ‘0203%‘ 11 GROUP BY EB.MNG_ORG_CODE) A 12 ON UO.ORG_CODE = A.ORG_CODE 13 LEFT JOIN (/*65岁以上老人免费体检*/ 14 SELECT EB.MNG_ORG_CODE ORG_CODE, 15 COUNT(DISTINCT EB.EHR_ID) LNTJS 16 FROM EHR_BASE EB, SVC_EXAM_1 SE 17 WHERE EB.EHR_ID = SE.EHR_ID 18 AND SE.TP_OLD = ‘1‘ 19 AND SE.EXAM_DATE >= TO_DATE(‘2015-01-01‘,‘YYYY-MM-DD‘) 20 AND SE.EXAM_DATE <= TO_DATE(‘2015-03-30‘,‘YYYY-MM-DD‘) 21 AND EB.MNG_ORG_CODE LIKE ‘0203%‘ 22 GROUP BY EB.MNG_ORG_CODE) B 23 ON UO.ORG_CODE = B.ORG_CODE 24 LEFT JOIN (/*新建健康档案*/ 25 SELECT EB.MNG_ORG_CODE ORG_CODE, 26 COUNT(1) XJDAS 27 FROM EHR_BASE EB 28 WHERE EB.DATE_CREATED >= TO_DATE(‘2015-01-01‘,‘YYYY-MM-DD‘) 29 AND EB.DATE_CREATED <= TO_DATE(‘2015-03-30‘,‘YYYY-MM-DD‘) 30 AND EB.MNG_ORG_CODE LIKE ‘0203%‘ 31 GROUP BY EB.MNG_ORG_CODE) C 32 ON UO.ORG_CODE = C.ORG_CODE 33 LEFT JOIN (/*健康档案使用*/ 34 SELECT EB.MNG_ORG_CODE ORG_CODE, 35 COUNT(1) DASYS 36 FROM EHR_BASE EB 37 WHERE EB.LAST_MNG_DATE > ADD_MONTHS(SYSDATE, -12) 38 AND EB.LAST_MNG_DATE <= SYSDATE 39 AND EB.MNG_ORG_CODE LIKE ‘0203%‘ 40 GROUP BY EB.MNG_ORG_CODE) D 41 ON UO.ORG_CODE = D.ORG_CODE 42 LEFT JOIN (/*高血压患者随访*/ 43 SELECT EB.MNG_ORG_CODE ORG_CODE, 44 COUNT(DISTINCT EB.EHR_ID) GXYSFS 45 FROM EHR_BASE EB, SVC_FLW_CHRONIC SFC 46 WHERE EB.EHR_ID = SFC.EHR_ID 47 AND SFC.CHRONIC_TYPE IN (‘1‘, ‘3‘) 48 AND SFC.DATE_CREATED >= TO_DATE(‘2015-01-01‘,‘YYYY-MM-DD‘) 49 AND SFC.DATE_CREATED <= TO_DATE(‘2015-03-30‘,‘YYYY-MM-DD‘) 50 AND EB.MNG_ORG_CODE LIKE ‘0203%‘ 51 GROUP BY EB.MNG_ORG_CODE) E 52 ON UO.ORG_CODE = E.ORG_CODE 53 LEFT JOIN (/*2型糖尿病随访*/ 54 SELECT EB.MNG_ORG_CODE ORG_CODE, 55 COUNT(DISTINCT EB.EHR_ID) TNBSFS 56 FROM EHR_BASE EB, SVC_FLW_CHRONIC SFC 57 WHERE EB.EHR_ID = SFC.EHR_ID 58 AND SFC.CHRONIC_TYPE IN (‘2‘, ‘3‘) 59 AND SFC.DATE_CREATED >= TO_DATE(‘2015-01-01‘,‘YYYY-MM-DD‘) 60 AND SFC.DATE_CREATED <= TO_DATE(‘2015-03-30‘,‘YYYY-MM-DD‘) 61 AND EB.MNG_ORG_CODE LIKE ‘0203%‘ 62 GROUP BY EB.MNG_ORG_CODE) F 63 ON UO.ORG_CODE = F.ORG_CODE 64 LEFT JOIN (/*合格健康档案数*/ 65 SELECT EB.MNG_ORG_CODE ORG_CODE, 66 COUNT(1) HGDAS 67 FROM EHR_BASE EB 68 WHERE EB.FILE_SPECIFICATION = ‘1‘ 69 AND EB.MNG_ORG_CODE LIKE ‘0203%‘ 70 AND EB.DEATH = ‘1‘ 71 AND EB.CANCELLED = ‘1‘ 72 AND EB.MOVED_OUT = ‘1‘ 73 GROUP BY EB.MNG_ORG_CODE) G 74 ON UO.ORG_CODE = G.ORG_CODE 75 LEFT JOIN (/*社区机构管理的高血压人数*/ 76 SELECT EB.MNG_ORG_CODE ORG_CODE, 77 COUNT(1) GXYGLS 78 FROM EHR_BASE EB 79 WHERE EB.CD_HYPERTENSION = ‘1‘ 80 AND EB.LAST_MNG_DATE > ADD_MONTHS(SYSDATE, -12) 81 AND EB.LAST_MNG_DATE <= SYSDATE 82 AND EB.DEATH = ‘1‘ 83 AND EB.CANCELLED = ‘1‘ 84 AND EB.MOVED_OUT = ‘1‘ 85 GROUP BY EB.MNG_ORG_CODE) H 86 ON UO.ORG_CODE = H.ORG_CODE 87 LEFT JOIN (/*高血压规范管理人数*/ 88 SELECT EB.MNG_ORG_CODE ORG_CODE, 89 COUNT(DISTINCT SFC.EHR_ID) GXYGFS 90 FROM EHR_BASE EB 91 INNER JOIN (SELECT EHR_ID 92 FROM SVC_FLW_CHRONIC 93 WHERE DATE_CREATED > ADD_MONTHS(SYSDATE, -12) 94 AND DATE_CREATED <= SYSDATE 95 AND CHRONIC_TYPE IN (‘1‘, ‘3‘) 96 AND FLW_ORG_CODE LIKE ‘0203%‘ 97 GROUP BY EHR_ID 98 HAVING COUNT(EHR_ID) = 4) SFC 99 ON EB.EHR_ID = SFC.EHR_ID 100 WHERE EB.CD_HYPERTENSION = ‘1‘ 101 AND EB.MNG_ORG_CODE LIKE ‘0203%‘ 102 AND EB.DEATH = ‘1‘ 103 AND EB.CANCELLED = ‘1‘ 104 AND EB.MOVED_OUT = ‘1‘ 105 GROUP BY EB.MNG_ORG_CODE) I 106 ON UO.ORG_CODE = I.ORG_CODE 107 LEFT JOIN (/*社区机构管理的糖尿病人数*/ 108 SELECT EB.MNG_ORG_CODE ORG_CODE, 109 COUNT(1) TNBGLS 110 FROM EHR_BASE EB 111 WHERE EB.CD_DIABETES_MELLITUS = ‘1‘ 112 AND EB.LAST_MNG_DATE > ADD_MONTHS(SYSDATE, -12) 113 AND EB.LAST_MNG_DATE <= SYSDATE 114 AND EB.DEATH = ‘1‘ 115 AND EB.CANCELLED = ‘1‘ 116 AND EB.MOVED_OUT = ‘1‘ 117 GROUP BY EB.MNG_ORG_CODE) J 118 ON UO.ORG_CODE = J.ORG_CODE 119 LEFT JOIN (/*糖尿病规范管理人数*/ 120 SELECT EB.MNG_ORG_CODE ORG_CODE, 121 COUNT(DISTINCT SFC.EHR_ID) TNBGFS 122 FROM EHR_BASE EB 123 INNER JOIN (SELECT EHR_ID 124 FROM SVC_FLW_CHRONIC 125 WHERE DATE_CREATED > ADD_MONTHS(SYSDATE, -12) 126 AND DATE_CREATED <= SYSDATE 127 AND CHRONIC_TYPE IN (‘2‘, ‘3‘) 128 AND FLW_ORG_CODE LIKE ‘0203%‘ 129 GROUP BY EHR_ID 130 HAVING COUNT(EHR_ID) = 4) SFC 131 ON EB.EHR_ID = SFC.EHR_ID 132 WHERE EB.CD_DIABETES_MELLITUS = ‘1‘ 133 AND EB.MNG_ORG_CODE LIKE ‘0203%‘ 134 AND EB.DEATH = ‘1‘ 135 AND EB.CANCELLED = ‘1‘ 136 AND EB.MOVED_OUT = ‘1‘ 137 GROUP BY EB.MNG_ORG_CODE) K 138 ON UO.ORG_CODE = K.ORG_CODE 139 WHERE UO.ORG_CODE LIKE ‘0203%‘ 140 ORDER BY UO.ORG_CODE
郑重声明：本站内容如果来自互联网及其他传播媒体，其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享，并不代表本站赞同其观点和对其真实性负责，也不构成任何其他建议。
