1 ----------------------------------------------------------------------------------------- 2 /* 3 综合查询： 4 查询条件： 5 1：管理机构（包含下级）：新街口街道赵登禹路社区卫生服务站 6 2：慢病分类（包含或）：高血压 7 查询结果：1862 8 */ 9 SELECT COUNT(*) 10 FROM ( 11 SELECT 12 EB.ID, 13 EB.EHR_ID, 14 EB.PERSON_NAME, 15 EB.GENDER, 16 TO_CHAR(EB.BIRTH_DATE,'YYYY-MM-DD') AS BIRTH_DATE, 17 (FLOOR(MONTHS_BETWEEN(SYSDATE,EB.BIRTH_DATE)/12)) AS AGE, 18 EB.PHONE_NUMBER, 19 EB.CREATOR, 20 TO_CHAR(EB.DATE_CREATED,'YYYY-MM-DD') AS DATE_CREATED, 21 EB.CREATING_ORG_NAME, 22 EB.DUTY_DOCTOR 23 FROM EHR_BASE EB 24 WHERE EB.MNG_ORG_CODE LIKE '020303%' 25 AND CD_HYPERTENSION ='1' 26 AND EB.CANCELLED = '1' 27 AND EB.DEATH = '1' 28 AND EB.MOVED_OUT = '1' 29 ); 30 /* 31 慢病种类分布： 32 查询条件：管理机构（包含下级）：新街口街道赵登禹路社区卫生服务站 33 查询结果： 34 COUNT_高血压 COUNT_糖尿病 COUNT_冠心病 COUNT_脑卒中 COUNT_其他 35 --------- --------- --------- --------- -------- 36 1862 837 565 261 734 37 */ 38 SELECT 39 SUM( 40 CASE 41 WHEN EB.CD_HYPERTENSION = '1' 42 THEN 1 43 ELSE 0 44 END) AS COUNT_高血压, 45 SUM( 46 CASE 47 WHEN EB.CD_DIABETES_MELLITUS = '1' 48 THEN 1 49 ELSE 0 50 END) AS COUNT_糖尿病, 51 SUM( 52 CASE 53 WHEN EB.CD_CORONARY_DISEASE = '1' 54 THEN 1 55 ELSE 0 56 END) AS COUNT_冠心病, 57 SUM( 58 CASE 59 WHEN EB.CD_CEREBRAL_APOPLEXY = '1' 60 THEN 1 61 ELSE 0 62 END) AS COUNT_脑卒中, 63 SUM( 64 CASE 65 WHEN EB.CD_OTHER = '1' 66 THEN 1 67 ELSE 0 68 END) AS COUNT_其他 69 FROM EHR_BASE EB 70 WHERE EB.CANCELLED = '1' 71 AND EB.DEATH = '1' 72 AND EB.MOVED_OUT = '1' 73 AND EB.GR_CHRONIC_DISEASE = '1' 74 AND EB.MNG_ORG_CODE LIKE '020303%'; 75 /* 76 综合查询： 77 查询条件： 78 1：管理机构（包含下级）：新街口街道赵登禹路社区卫生服务站 79 2：年龄（范围以内）：65-79岁 80 3：健康分类（包含或）：慢病 81 查询结果：1862 82 */ 83 SELECT COUNT(*) FROM ( 84 SELECT 85 EB.ID, 86 EB.EHR_ID, 87 EB.PERSON_NAME, 88 EB.GENDER, 89 TO_CHAR(EB.BIRTH_DATE,'YYYY-MM-DD') AS BIRTH_DATE, 90 (FLOOR(MONTHS_BETWEEN(SYSDATE,EB.BIRTH_DATE)/12)) AS AGE, 91 EB.PHONE_NUMBER, 92 EB.CREATOR, 93 TO_CHAR(EB.DATE_CREATED,'YYYY-MM-DD') AS DATE_CREATED, 94 EB.CREATING_ORG_NAME, 95 EB.DUTY_DOCTOR 96 FROM EHR_BASE EB 97 WHERE ((FLOOR(MONTHS_BETWEEN(SYSDATE,EB.BIRTH_DATE)/12)) >= 65 98 AND (FLOOR(MONTHS_BETWEEN(SYSDATE,EB.BIRTH_DATE)/12)) <= 79) 99 AND EB.MNG_ORG_CODE LIKE '020303%'100 AND GR_CHRONIC_DISEASE ='1'101 AND EB.CANCELLED = '1'102 AND EB.DEATH = '1'103 AND EB.MOVED_OUT = '1'104 );105 /*106 慢病年龄分布：107 查询条件：管理机构（包含下级）：新街口街道赵登禹路社区卫生服务站108 查询结果：109 COUNT_34岁以下 COUNT_35_44 COUNT_45_54 COUNT_55_59 COUNT_60_64 COUNT_65_79 COUNT_80岁以上 110 //----------- ----------- ----------- ----------- ----------- ----------- ----------- 111 9 36 235 300 336 786 384 112 */113 SELECT114 SUM(115 CASE116 WHEN EB.BIRTH_DATE > TO_DATE('1980-04-03','YYYY-MM-DD')117 THEN 1118 ELSE 0119 END) AS COUNT_34岁以下,120 SUM(121 CASE122 WHEN EB.BIRTH_DATE > TO_DATE('1970-04-03','YYYY-MM-DD')123 AND EB.BIRTH_DATE <= TO_DATE('1980-04-03','YYYY-MM-DD')124 THEN 1125 ELSE 0126 END) AS COUNT_35_44,127 SUM(128 CASE129 WHEN EB.BIRTH_DATE > TO_DATE('1960-04-03','YYYY-MM-DD')130 AND EB.BIRTH_DATE <= TO_DATE('1970-04-03','YYYY-MM-DD')131 THEN 1132 ELSE 0133 END) AS COUNT_45_54,134 SUM(135 CASE136 WHEN EB.BIRTH_DATE > TO_DATE('1955-04-03','YYYY-MM-DD')137 AND EB.BIRTH_DATE <= TO_DATE('1960-04-03','YYYY-MM-DD')138 THEN 1139 ELSE 0140 END) AS COUNT_55_59,141 SUM(142 CASE143 WHEN EB.BIRTH_DATE > TO_DATE('1950-04-03','YYYY-MM-DD')144 AND EB.BIRTH_DATE <= TO_DATE('1955-04-03','YYYY-MM-DD')145 THEN 1146 ELSE 0147 END) AS COUNT_60_64,148 SUM(149 CASE150 WHEN EB.BIRTH_DATE > TO_DATE('1935-04-03','YYYY-MM-DD')151 AND EB.BIRTH_DATE <= TO_DATE('1950-04-03','YYYY-MM-DD')152 THEN 1153 ELSE 0154 END) AS COUNT_65_79,155 SUM(156 CASE157 WHEN EB.BIRTH_DATE <= TO_DATE('1935-04-03','YYYY-MM-DD')158 THEN 1159 ELSE 0160 END) AS COUNT_80岁以上161 FROM EHR_BASE EB162 WHERE EB.CANCELLED = '1'163 AND EB.DEATH = '1'164 AND EB.MOVED_OUT = '1'165 AND EB.GR_CHRONIC_DISEASE = '1'166 AND EB.MNG_ORG_CODE LIKE '020303%';167 /*168 本机构高血压情况统计（2个扇形图：管理率、血压控制率）：169 查询条件：管理机构（包含下级）：新街口街道赵登禹路社区卫生服务站170 查询结果：171 发病数 管理数 末次血压达标数172 FBS GLS MCXYDBS 173 ----- ---- ------- 174 15734 4687 4237 175 */176 SELECT SUM(B.FBS) FBS, SUM(B.GLS) GLS, SUM(B.MCXYDBS) MCXYDBS177 FROM(178 SELECT UO.HYPERTENSION_COUNT FBS, A.GLS, A.MCXYDBS179 FROM UMS_ORG UO180 LEFT JOIN(181 SELECT EB.MNG_ORG_CODE,182 SUM(183 CASE184 WHEN EB.CD_HYPERTENSION = '1'185 AND LAST_MNG_DATE>=TO_DATE('2015-01-01','YYYY-MM-DD')186 AND LAST_MNG_DATE<=TO_DATE('2015-12-31','YYYY-MM-DD')187 THEN 1188 ELSE 0189 END) AS GLS,190 SUM(191 CASE192 WHEN EB.CD_HYPERTENSION = '1'193 AND EHI.SBP_L < 140194 AND EHI.SBP_L >= 0195 AND EHI.DBP_L < 90196 AND EHI.DBP_L >= 0197 AND LAST_MNG_DATE>=TO_DATE('2015-01-01','YYYY-MM-DD')198 AND LAST_MNG_DATE<=TO_DATE('2015-12-31','YYYY-MM-DD')199 THEN 1200 ELSE 0201 END) AS MCXYDBS202 FROM EHR_BASE EB, EHR_HF_INDICATOR EHI203 WHERE EB.EHR_ID=EHI.EHR_ID204 AND EB.CANCELLED = '1'205 AND EB.DEATH = '1'206 AND EB.MOVED_OUT = '1'207 AND EB.MNG_ORG_CODE LIKE '0203%'208 GROUP BY EB.MNG_ORG_CODE 209 ) A210 ON UO.ORG_CODE = A.MNG_ORG_CODE211 WHERE UO.ORG_CODE LIKE '0203%'212 ) B;213 /*214 所辖机构高血压情况统计：215 查询条件：管理机构（包含下级）：新街口街道赵登禹路社区卫生服务站216 查询结果：217 ORGNAME ORG_SHORT_NAME FBS GLS MCXYDBS 218 ----------------- -------------- ------ ------ ------- 219 北京市西城区新街口社区卫生服务中心 新街口中心 15734 1642 1549 220 新街口街道西四北六条社区卫生服务站 西四北六条 (null) 0 0 221 新街口街道西三条社区卫生服务站 (null) (null) (null) (null) 222 新街口街道赵登禹路社区卫生服务站 (null) (null) 1778 1524 223 新街口街道官园社区卫生服务站 (null) (null) 1267 1164 224 新街口街道玉桃园社区卫生服务站 (null) (null) (null) (null) 225 新街口街道西里社区卫生服务站 (null) (null) (null) (null) 226 */227 SELECT UO.ORG_NAME ORGNAME, UO.ORG_SHORT_NAME, UO.HYPERTENSION_COUNT FBS, A.GLS, A.MCXYDBS FROM UMS_ORG UO228 LEFT JOIN 229 (230 SELECT EB.MNG_ORG_CODE,231 SUM(CASE 232 WHEN EB.CD_HYPERTENSION = '1' 233 AND LAST_MNG_DATE>=TO_DATE('2015-01-01','YYYY-MM-DD') 234 AND LAST_MNG_DATE<=TO_DATE('2015-12-31','YYYY-MM-DD') 235 THEN 1 ELSE 0 END) AS GLS,236 SUM(CASE 237 WHEN EB.CD_HYPERTENSION = '1' 238 AND EHI.SBP_L < 140 239 AND EHI.SBP_L >= 0 240 AND EHI.DBP_L < 90 241 AND EHI.DBP_L >=0 242 AND LAST_MNG_DATE>=TO_DATE('2015-01-01','YYYY-MM-DD') 243 AND LAST_MNG_DATE<=TO_DATE('2015-12-31','YYYY-MM-DD') 244 THEN 1 ELSE 0 END) AS MCXYDBS245 FROM EHR_BASE EB,EHR_HF_INDICATOR EHI 246 WHERE EB.EHR_ID=EHI.EHR_ID247 AND EB.CANCELLED = '1' 248 AND EB.DEATH = '1' 249 AND EB.MOVED_OUT = '1'250 AND EB.MNG_ORG_CODE LIKE '0203%'251 GROUP BY EB.MNG_ORG_CODE252 ) A ON UO.ORG_CODE = A.MNG_ORG_CODE253 WHERE UO.ORG_CODE LIKE '0203%';254 /*255 本机构糖尿病情况统计（2个扇形图：管理率、血糖控制率）：256 查询条件：管理机构（包含下级）：新街口街道赵登禹路社区卫生服务站257 查询结果：258 FBS GLS MCXTDBS 259 ---- ---- ------- 260 2325 2063 1688 261 */262 SELECT SUM(B.FBS) FBS,SUM(B.GLS) GLS,SUM(B.MCXTDBS) MCXTDBS FROM (263 SELECT UO.DIABETES_COUNT FBS, A.GLS, A.MCXTDBS FROM UMS_ORG UO264 LEFT JOIN 265 (266 SELECT EB.MNG_ORG_CODE,267 SUM(CASE 268 WHEN EB.CD_DIABETES_MELLITUS = '1' 269 AND LAST_MNG_DATE>=TO_DATE('2015-01-01','YYYY-MM-DD') 270 AND LAST_MNG_DATE<=TO_DATE('2015-12-31','YYYY-MM-DD') 271 THEN 1 ELSE 0 272 END) AS GLS,273 SUM(CASE 274 WHEN EB.CD_DIABETES_MELLITUS = '1' 275 AND EHI.FBG < 7 276 AND EHI.FBG >= 0 277 AND LAST_MNG_DATE>=TO_DATE('2015-01-01','YYYY-MM-DD') 278 AND LAST_MNG_DATE<=TO_DATE('2015-12-31','YYYY-MM-DD') 279 THEN 1 ELSE 0 280 END) AS MCXTDBS281 FROM EHR_BASE EB,EHR_HF_INDICATOR EHI 282 WHERE EB.EHR_ID=EHI.EHR_ID283 AND EB.CANCELLED = '1' 284 AND EB.DEATH = '1' 285 AND EB.MOVED_OUT = '1'286 AND EB.MNG_ORG_CODE LIKE '0203%'287 GROUP BY EB.MNG_ORG_CODE288 ) A ON UO.ORG_CODE = A.MNG_ORG_CODE289 WHERE UO.ORG_CODE LIKE '0203%'290 ) B;291 /*292 所辖机构糖尿病情况统计：293 查询条件：管理机构（包含下级）：新街口街道赵登禹路社区卫生服务站294 查询结果：295 ORGNAME ORG_SHORT_NAME FBS GLS MCXTDBS 296 ----------------- -------------- ------ ------ ------- 297 北京市西城区新街口社区卫生服务中心 新街口中心 2325 716 598 298 新街口街道西四北六条社区卫生服务站 西四北六条 (null) 0 0 299 新街口街道西三条社区卫生服务站 (null) (null) (null) (null) 300 新街口街道赵登禹路社区卫生服务站 (null) (null) 802 647 301 新街口街道官园社区卫生服务站 (null) (null) 545 443 302 新街口街道玉桃园社区卫生服务站 (null) (null) (null) (null) 303 新街口街道西里社区卫生服务站 (null) (null) (null) (null) 304 */305 SELECT UO.ORG_NAME ORGNAME, UO.ORG_SHORT_NAME, UO.DIABETES_COUNT FBS, A.GLS, A.MCXTDBS FROM UMS_ORG UO306 LEFT JOIN 307 (308 SELECT EB.MNG_ORG_CODE,309 SUM(CASE 310 WHEN EB.CD_DIABETES_MELLITUS = '1' 311 AND LAST_MNG_DATE>=TO_DATE('2015-01-01','YYYY-MM-DD') 312 AND LAST_MNG_DATE<=TO_DATE('2015-12-31','YYYY-MM-DD') 313 THEN 1 ELSE 0 314 END) AS GLS,315 SUM(CASE 316 WHEN EB.CD_DIABETES_MELLITUS = '1' 317 AND EHI.FBG < 7 318 AND EHI.FBG >= 0 319 AND LAST_MNG_DATE>=TO_DATE('2015-01-01','YYYY-MM-DD') 320 AND LAST_MNG_DATE<=TO_DATE('2015-12-31','YYYY-MM-DD') 321 THEN 1 ELSE 0 322 END) AS MCXTDBS323 FROM EHR_BASE EB,EHR_HF_INDICATOR EHI 324 WHERE EB.EHR_ID=EHI.EHR_ID325 AND EB.CANCELLED = '1' 326 AND EB.DEATH = '1' 327 AND EB.MOVED_OUT = '1'328 AND EB.MNG_ORG_CODE LIKE '0203%'329 GROUP BY EB.MNG_ORG_CODE330 ) A ON UO.ORG_CODE = A.MNG_ORG_CODE331 WHERE UO.ORG_CODE LIKE '0203%';332 /*333 综合查询：334 查询条件：335 1：管理机构（包含下级）：北京市西城区新街口社区卫生服务中心336 2：慢病随访（范围以内）：2014-04-04到2015-04-03，4次及以上337 查询结果：1222338 */339 SELECT COUNT(*) FROM (340 SELECT341 EB.ID,342 EB.EHR_ID,343 EB.PERSON_NAME,344 EB.GENDER,345 TO_CHAR(EB.BIRTH_DATE,'YYYY-MM-DD') AS BIRTH_DATE,346 (FLOOR(MONTHS_BETWEEN(SYSDATE,EB.BIRTH_DATE)/12)) AS AGE,347 EB.PHONE_NUMBER,348 EB.CREATOR,349 TO_CHAR(EB.DATE_CREATED,'YYYY-MM-DD') AS DATE_CREATED,350 EB.CREATING_ORG_NAME,351 EB.DUTY_DOCTOR352 FROM EHR_BASE EB353 LEFT JOIN354 (355 SELECT EHR_ID, COUNT(EHR_ID) V_COUNT356 FROM SVC_FLW_CHRONIC357 WHERE (DATE_CREATED >= TO_DATE('2014-04-04','yyyy-mm-dd') AND DATE_CREATED <= TO_DATE('2015-04-03','yyyy-mm-dd'))358 GROUP BY EHR_ID 359 ) SFC360 ON (EB.EHR_ID = SFC.EHR_ID)361 WHERE EB.MNG_ORG_CODE LIKE '0203%'362 AND SFC.V_COUNt >= 4363 AND EB.CANCELLED = '1'364 AND EB.DEATH = '1'365 AND EB.MOVED_OUT = '1'366 );367 /*368 档案人群分布（健康、高危、慢病）（柱形图）：369 查询条件：管理机构（包含子机构）：北京市西城区新街口社区卫生服务中心370 查询结果：371 COUNT_健康 COUNT_高危 COUNT_慢病 372 -------- -------- -------- 373 27825 3907 5541 374 375 */376 SELECT377 CASE WHEN SUM(TO_NUMBER(EB.GR_HEALTH)) IS NULL THEN 0 ELSE SUM(TO_NUMBER(EB.GR_HEALTH)) END AS COUNT_健康,378 CASE WHEN SUM(TO_NUMBER(EB.GR_HIGH_RISK)) IS NULL THEN 0 ELSE SUM(TO_NUMBER(EB.GR_HIGH_RISK)) END AS COUNT_高危,379 CASE WHEN SUM(TO_NUMBER(EB.GR_CHRONIC_DISEASE)) IS NULL THEN 0 ELSE SUM(TO_NUMBER(EB.GR_CHRONIC_DISEASE)) END AS COUNT_慢病380 FROM EHR_BASE EB 381 WHERE EB.CANCELLED = '1' 382 AND EB.DEATH = '1' 383 AND EB.MOVED_OUT = '1' 384 AND EB.MNG_ORG_CODE LIKE '0203%';385 /*386 档案人群分布（儿童、孕产妇、老年人、精神病、残疾人和其他）（柱形图）：387 查询条件：管理机构（包含子机构）：北京市西城区新街口社区卫生服务中心388 查询结果：389 COUNT_儿童 COUNT_孕产妇 COUNT_老年人 COUNT_精神病 COUNT_残疾人 COUNT_其他 390 -------- --------- --------- --------- --------- -------- 391 30 0 9503 1 246 27595 392 393 */394 SELECT395 CASE WHEN SUM(TO_NUMBER(EB.GR_CHILDREN)) IS NULL THEN 0 ELSE SUM(TO_NUMBER(EB.GR_CHILDREN)) END AS COUNT_儿童,396 CASE WHEN SUM(TO_NUMBER(EB.GR_MATERNITY)) IS NULL THEN 0 ELSE SUM(TO_NUMBER(EB.GR_MATERNITY)) END AS COUNT_孕产妇,397 CASE WHEN SUM(TO_NUMBER(EB.GR_OLD)) IS NULL THEN 0 ELSE SUM(TO_NUMBER(EB.GR_OLD)) END AS COUNT_老年人,398 CASE WHEN SUM(TO_NUMBER(EB.GR_MENTAL_DISORDER)) IS NULL THEN 0 ELSE SUM(TO_NUMBER(EB.GR_MENTAL_DISORDER)) END AS COUNT_精神病,399 CASE WHEN SUM(TO_NUMBER(EB.GR_HANDICAPPED)) IS NULL THEN 0 ELSE SUM(TO_NUMBER(EB.GR_HANDICAPPED)) END AS COUNT_残疾人,400 CASE WHEN SUM(CASE WHEN EB.GR_CHILDREN='0' 401 AND EB.GR_MATERNITY='0' 402 AND EB.GR_OLD='0' 403 AND EB.GR_MENTAL_DISORDER='0' 404 AND EB.GR_HANDICAPPED='0' 405 THEN 1 ELSE 0 END) IS NULL THEN 0 ELSE 406 SUM(CASE WHEN EB.GR_CHILDREN='0' 407 AND EB.GR_MATERNITY='0' 408 AND EB.GR_OLD='0' 409 AND EB.GR_MENTAL_DISORDER='0' 410 AND EB.GR_HANDICAPPED='0' 411 THEN 1 ELSE 0 END) END AS COUNT_其他412 FROM EHR_BASE EB 413 WHERE EB.CANCELLED = '1' 414 AND EB.DEATH = '1' 415 AND EB.MOVED_OUT = '1' 416 AND EB.MNG_ORG_CODE LIKE '0203%';417 /*418 档案年龄分布（柱形图）：419 查询条件：管理机构（包含子机构）：北京市西城区新街口社区卫生服务中心420 查询结果：421 COUNT_0_6岁 COUNT_7_17岁 COUNT_18_34岁 COUNT_35_44岁 COUNT_45_54岁 COUNT_55_64岁 COUNT_65_79岁 COUNT_80岁及以上 422 ---------- ----------- ------------ ------------ ------------ ------------ ------------ ------------ 423 48 566 5460 4848 7515 9880 6246 2710 424 425 */426 SELECT427 SUM(428 CASE429 WHEN EB.BIRTH_DATE > TO_DATE('2008-04-04','YYYY-MM-DD')430 THEN 1431 ELSE 0432 END) AS COUNT_0_6岁,433 SUM(434 CASE435 WHEN EB.BIRTH_DATE > TO_DATE('1997-04-04','YYYY-MM-DD')436 AND EB.BIRTH_DATE <= TO_DATE('2008-04-04','YYYY-MM-DD')437 THEN 1438 ELSE 0439 END) AS COUNT_7_17岁,440 SUM(441 CASE442 WHEN EB.BIRTH_DATE > TO_DATE('1980-04-04','YYYY-MM-DD')443 AND EB.BIRTH_DATE <= TO_DATE('1997-04-04','YYYY-MM-DD')444 THEN 1445 ELSE 0446 END) AS COUNT_18_34岁,447 SUM(448 CASE449 WHEN EB.BIRTH_DATE > TO_DATE('1970-04-04','YYYY-MM-DD')450 AND EB.BIRTH_DATE <= TO_DATE('1980-04-04','YYYY-MM-DD')451 THEN 1452 ELSE 0453 END) AS COUNT_35_44岁,454 SUM(455 CASE456 WHEN EB.BIRTH_DATE > TO_DATE('1960-04-04','YYYY-MM-DD')457 AND EB.BIRTH_DATE <= TO_DATE('1970-04-04','YYYY-MM-DD')458 THEN 1459 ELSE 0460 END) AS COUNT_45_54岁,461 SUM(462 CASE463 WHEN EB.BIRTH_DATE > TO_DATE('1950-04-04','YYYY-MM-DD')464 AND EB.BIRTH_DATE <= TO_DATE('1960-04-04','YYYY-MM-DD')465 THEN 1466 ELSE 0467 END) AS COUNT_55_64岁,468 SUM(469 CASE470 WHEN EB.BIRTH_DATE > TO_DATE('1935-04-04','YYYY-MM-DD')471 AND EB.BIRTH_DATE <= TO_DATE('1950-04-04','YYYY-MM-DD')472 THEN 1473 ELSE 0474 END) AS COUNT_65_79岁,475 SUM(476 CASE477 WHEN EB.BIRTH_DATE <= TO_DATE('1935-04-04','YYYY-MM-DD')478 THEN 1479 ELSE 0480 END) AS COUNT_80岁及以上481 FROM EHR_BASE EB482 WHERE EB.CANCELLED = '1'483 AND EB.DEATH = '1'484 AND EB.MOVED_OUT = '1'485 AND EB.MNG_ORG_CODE LIKE '0203%';486 /*487 老年人管理情况统计（老年人健康状况占比情况）（饼图）：488 查询条件：管理机构（包含子机构）：北京市西城区新街口社区卫生服务中心489 查询结果：490 COUNT_健康 COUNT_高危 COUNT_慢病 491 -------- -------- -------- 492 5181 1028 3294 493 */494 SELECT495 SUM(496 CASE497 WHEN GR_OLD = '1'498 AND GR_HEALTH = '1'499 THEN 1500 ELSE 0501 END) AS COUNT_健康,502 SUM(503 CASE504 WHEN GR_OLD = '1'505 AND GR_HIGH_RISK = '1'506 THEN 1507 ELSE 0508 END) AS COUNT_高危,509 SUM(510 CASE511 WHEN GR_OLD = '1'512 AND GR_CHRONIC_DISEASE = '1'513 THEN 1514 ELSE 0515 END) AS COUNT_慢病516 FROM EHR_BASE EB517 WHERE EB.CANCELLED = '1'518 AND EB.DEATH = '1'519 AND EB.MOVED_OUT = '1'520 AND MNG_ORG_CODE LIKE '0203%';521 /*522 老年人管理情况统计（老年血压控制率、老年血糖控制率）（2个扇形图）：523 查询条件：管理机构（包含子机构）：北京市西城区新街口社区卫生服务中心524 查询结果：525 GXY MCXYDBS TNB MCXTDBS 526 ---- ------- ---- ------- 527 2872 2567 1269 1000 528 */529 SELECT 530 SUM(531 CASE 532 WHEN EB.GR_OLD = '1' 533 AND EB.CD_HYPERTENSION = '1' 534 THEN 1 ELSE 0 535 END) AS GXY,536 SUM(537 CASE 538 WHEN EB.GR_OLD = '1' 539 AND EB.CD_HYPERTENSION = '1' 540 AND EHI.SBP_L < 140 541 AND EHI.SBP_L > 0 542 AND EHI.DBP_L < 90 543 AND EHI.DBP_L > 0 544 THEN 1 ELSE 0 545 END) AS MCXYDBS,546 SUM(547 CASE548 WHEN EB.GR_OLD = '1'549 AND EB.CD_DIABETES_MELLITUS = '1'550 THEN 1 ELSE 0551 END) AS TNB,552 SUM(553 CASE554 WHEN EB.GR_OLD = '1'555 AND EB.CD_DIABETES_MELLITUS = '1'556 AND EHI.FBG < 7557 AND EHI.FBG>0558 THEN 1 ELSE 0559 END) AS MCXTDBS560 FROM EHR_BASE EB, EHR_HF_INDICATOR EHI561 WHERE EB.EHR_ID = EHI.EHR_ID562 AND EB.CANCELLED = '1'563 AND EB.DEATH = '1'564 AND EB.MOVED_OUT = '1'565 AND EB.MNG_ORG_CODE LIKE '0203%';566 /*567 老年人慢病种类分布（柱形图）：568 查询条件：管理机构（包含子机构）：北京市西城区新街口社区卫生服务中心569 查询结果：570 COUNT_高血压 COUNT_糖尿病 COUNT_冠心病 COUNT_脑卒中 COUNT_其他 571 --------- --------- --------- --------- -------- 572 2872 1269 1340 574 2403 573 */574 SELECT575 SUM(CASE WHEN EB.GR_OLD = '1' AND EB.CD_HYPERTENSION = '1' THEN 1 ELSE 0 END) AS COUNT_高血压,576 SUM(CASE WHEN EB.GR_OLD = '1' AND EB.CD_DIABETES_MELLITUS = '1' THEN 1 ELSE 0 END) AS COUNT_糖尿病,577 SUM(CASE WHEN EB.GR_OLD = '1' AND EB.CD_CORONARY_DISEASE = '1' THEN 1 ELSE 0 END) AS COUNT_冠心病,578 SUM(CASE WHEN EB.GR_OLD = '1' AND EB.CD_CEREBRAL_APOPLEXY = '1' THEN 1 ELSE 0 END) AS COUNT_脑卒中,579 SUM(CASE WHEN EB.GR_OLD = '1' AND EB.CD_OTHER = '1' THEN 1 ELSE 0 END) AS COUNT_其他580 FROM EHR_BASE EB581 WHERE EB.CANCELLED = '1' 582 AND EB.DEATH = '1' 583 AND EB.MOVED_OUT = '1' 584 AND EB.MNG_ORG_CODE LIKE '0203%';585 /*--------------------------start--规范管理情况---------------------------------------------------------------------*/586 /*587 本页面有6个扇形图588 其中后面5个扇形图会分别用到下面的数据：589 --老年人数: 9498590 --精神病人数: 1591 --慢病人数: 5538592 --高血压人数: 4786593 --糖尿病人数: 2104594 595 查询结果：596 COUNT_老年人 COUNT_精神病 COUNT_慢病 COUNT_高血压 COUNT_糖尿病 597 --------- --------- -------- --------- --------- 598 9498 1 5538 4786 2104 599 */600 SELECT 601 SUM(TO_NUMBER(EB.GR_OLD)) AS COUNT_老年人,602 SUM(TO_NUMBER(EB.GR_MENTAL_DISORDER)) AS COUNT_精神病,603 SUM(TO_NUMBER(EB.GR_CHRONIC_DISEASE)) AS COUNT_慢病,604 SUM(TO_NUMBER(EB.CD_HYPERTENSION)) AS COUNT_高血压,605 SUM(TO_NUMBER(EB.CD_DIABETES_MELLITUS)) AS COUNT_糖尿病606 FROM EHR_BASE EB607 WHERE EB.CANCELLED = '1' 608 AND EB.DEATH = '1' 609 AND EB.MOVED_OUT = '1'610 AND EB.MNG_ORG_CODE LIKE '0203%'611 AND EB.EHR_INTEGRITY = 1;612 /*613 档案规范管理率（扇形图）：614 查询条件：管理机构（包含子机构）：北京市西城区新街口社区卫生服务中心615 */616 /*617 已建档案数:588618 查询结果：619 COUNT_已建档案620 ---------- 621 588622 */623 SELECT COUNT(1) AS COUNT_已建档案624 FROM EHR_BASE EB625 WHERE EB.CANCELLED = '1' 626 AND EB.DEATH = '1' 627 AND EB.MOVED_OUT = '1'628 AND EB.DATE_CREATED >= TO_DATE('2014-04-04','YYYY-MM-DD')629 AND EB.DATE_CREATED <= TO_DATE('2015-04-03','YYYY-MM-DD')630 AND EB.MNG_ORG_CODE LIKE '0203%';631 /*632 规范管理数:588633 查询结果：634 COUNT(DISTINCT(EB.EHR_ID)) 635 -------------------------- 636 588 637 */638 SELECT COUNT(DISTINCT(EB.EHR_ID))639 FROM EHR_BASE EB640 WHERE EB.EHR_INTEGRITY = 1641 AND EB.DATE_CREATED >= TO_DATE('2014-04-04','YYYY-MM-DD')642 AND EB.DATE_CREATED <= TO_DATE('2015-04-03','YYYY-MM-DD')643 AND EB.MNG_ORG_CODE LIKE '0203%'644 AND EB.EHR_INTEGRITY = 1645 AND DEATH='1'646 AND CANCELLED='1'647 AND MOVED_OUT='1';648 /* 档案规范管理率 = 规范管理数/已建档案数 = 100.00% */649 650 /*651 老年人规范管理率（扇形图）：652 查询条件：管理机构（包含子机构）：北京市西城区新街口社区卫生服务中心653 654 规范管理数:0655 查询结果：656 COUNT(DISTINCT(EB.EHR_ID)) 657 -------------------------- 658 0 659 */660 SELECT COUNT(DISTINCT(EB.EHR_ID)) 661 FROM EHR_BASE EB, SVC_ASM_OLD_A SAOA, SVC_ASM_OLD_S SAOS, SVC_EXAM_1 SE662 WHERE EB.EHR_ID=SAOA.EHR_ID 663 AND EB.EHR_ID=SAOS.EHR_ID 664 AND EB.EHR_ID=SE.EHR_ID 665 AND EB.GR_OLD='1'666 AND SAOA.ASM_DATE >= TO_DATE('2014-04-04','YYYY-MM-DD') 667 AND SAOA.ASM_DATE <= TO_DATE('2015-04-03','YYYY-MM-DD')668 AND SAOS.ASM_DATE >= TO_DATE('2014-04-04','YYYY-MM-DD') 669 AND SAOS.ASM_DATE <= TO_DATE('2015-04-03','YYYY-MM-DD')670 AND SE.EXAM_DATE >= TO_DATE('2014-04-04','YYYY-MM-DD')671 AND SE.EXAM_DATE <= TO_DATE('2015-04-03','YYYY-MM-DD')672 AND EB.MNG_ORG_CODE LIKE '0203%'673 AND EB.EHR_INTEGRITY = 1674 AND EB.EHR_ID IN (675 SELECT EHR_ID FROM SVC_ASM_OLD_S676 GROUP BY EHR_ID HAVING COUNT(1) >=1677 )678 AND EB.EHR_ID IN (679 SELECT EHR_ID FROM SVC_EXAM_1680 GROUP BY EHR_ID HAVING COUNT(1) >=1681 )682 /* 老年人规范管理率 = 规范管理数/老年人数 = 0.00% */683 684 /*685 精神病规范管理率（扇形图）：686 查询条件：687 1：管理机构（包含子机构）：北京市西城区新街口社区卫生服务中心688 2：时间范围：2014-04-04到2015-04-03689 规范管理数:0690 查询结果：691 COUNT(DISTINCT(EB.EHR_ID)) 692 -------------------------- 693 0694 */695 SELECT COUNT(DISTINCT(EB.EHR_ID)) 696 FROM EHR_BASE EB, PCF_MENTAL PM, SVC_FLW_MENTAL SFM, SVC_EXAM_1 SE697 WHERE EB.EHR_ID=PM.EHR_ID 698 AND EB.EHR_ID=SFM.EHR_ID 699 AND EB.EHR_ID=SE.EHR_ID 700 AND EB.GR_MENTAL_DISORDER='1'701 AND PM.DATE_CREATED >= TO_DATE('2014-04-04','YYYY-MM-DD') 702 AND PM.DATE_CREATED <= TO_DATE('2015-04-03','YYYY-MM-DD')703 AND SFM.DATE_CREATED >= TO_DATE('2014-04-04','YYYY-MM-DD') 704 AND SFM.DATE_CREATED <= TO_DATE('2015-04-03','YYYY-MM-DD')705 AND SE.EXAM_DATE >= TO_DATE('2014-04-04','YYYY-MM-DD') 706 AND SE.EXAM_DATE <= TO_DATE('2015-04-03','YYYY-MM-DD')707 AND EB.MNG_ORG_CODE LIKE '0203%'708 AND EB.EHR_INTEGRITY = 1709 AND EB.EHR_ID IN (710 SELECT EHR_ID FROM SVC_FLW_MENTAL711 GROUP BY EHR_ID HAVING COUNT(1) >=1712 )713 AND EB.EHR_ID IN (714 SELECT EHR_ID FROM SVC_EXAM_1715 GROUP BY EHR_ID HAVING COUNT(1) >=1716 )717 /* 精神病规范管理率 = 规范管理数/精神病人数 = 0.00% */718 /*719 慢病规范管理率（扇形图）：720 查询条件：721 1：管理机构（包含子机构）：北京市西城区新街口社区卫生服务中心722 2：时间范围：2014-04-04到2015-04-03723 规范管理数:2557724 查询结果：725 COUNT(DISTINCT(EB.EHR_ID)) 726 -------------------------- 727 2557 728 */729 SELECT COUNT(DISTINCT(EB.EHR_ID)) 730 FROM EHR_BASE EB, SVC_FLW_CHRONIC SFC731 WHERE EB.EHR_ID=SFC.EHR_ID 732 AND EB.GR_CHRONIC_DISEASE='1'733 AND SFC.DATE_CREATED >= TO_DATE('2014-04-04','YYYY-MM-DD')734 AND SFC.DATE_CREATED <= TO_DATE('2015-04-03','YYYY-MM-DD')735 AND EB.MNG_ORG_CODE LIKE '0203%'736 AND EB.EHR_INTEGRITY = 1737 AND EXISTS (738 SELECT EHR_ID 739 FROM SVC_FLW_CHRONIC740 WHERE SVC_FLW_CHRONIC.EHR_ID = EB.EHR_ID741 GROUP BY EHR_ID 742 HAVING COUNT(1) >=4743 )744 /* 慢病规范管理率 = 规范管理数/慢病人数 = 46.17% */745 746 /*747 慢病规范管理率（扇形图）：748 查询条件：749 1：管理机构（包含子机构）：北京市西城区新街口社区卫生服务中心750 2：档案完整度：完整751 3：时间范围：2014-04-04到2015-04-03752 4：慢病首诊：做过慢病首诊753 5：慢病随访：4次及以上754 6：居民体检：1次及以上755 7：慢病年度评估：1次及以上756 757 慢病规范管理数:758 查询结果：759 COUNT(DISTINCT(EB.EHR_ID)) 760 -------------------------- 761 1566762 */763 SELECT COUNT(DISTINCT(EB.EHR_ID)) 764 FROM EHR_BASE EB, SVC_FIRST_SOAP SFS, SVC_EXAM_1 SE, SVC_ASM_YEAR SAY, SVC_FLW_CHRONIC SFC765 WHERE EB.EHR_ID=SFS.EHR_ID 766 AND EB.EHR_ID=SE.EHR_ID 767 AND EB.EHR_ID=SAY.EHR_ID 768 AND EB.EHR_ID=SFC.EHR_ID 769 AND EB.GR_CHRONIC_DISEASE='1'770 771 AND SE.EXAM_DATE >= TO_DATE('2014-04-04','YYYY-MM-DD') 772 AND SE.EXAM_DATE <= TO_DATE('2015-04-03','YYYY-MM-DD')773 774 AND SAY.ASM_DATE >= TO_DATE('2014-04-04','YYYY-MM-DD') 775 AND SAY.ASM_DATE <= TO_DATE('2015-04-03','YYYY-MM-DD')776 777 AND SFC.DATE_CREATED >= TO_DATE('2014-04-04','YYYY-MM-DD') 778 AND SFC.DATE_CREATED <= TO_DATE('2015-04-03','YYYY-MM-DD')779 780 AND EB.MNG_ORG_CODE LIKE '0203%'781 AND EB.EHR_INTEGRITY = 1782 AND EB.EHR_ID IN (783 SELECT EHR_ID FROM SVC_EXAM_1784 GROUP BY EHR_ID HAVING COUNT(1) >=1785 )786 AND EB.EHR_ID IN (787 SELECT EHR_ID FROM SVC_ASM_YEAR788 GROUP BY EHR_ID HAVING COUNT(1) >=1789 )790 AND EXISTS (791 SELECT EHR_ID FROM SVC_FLW_CHRONIC792 WHERE SVC_FLW_CHRONIC.EHR_ID = EB.EHR_ID793 GROUP BY EHR_ID HAVING COUNT(1) >=4794 )795 /* 慢病规范管理率 = 规范管理数/慢病人数 = 1566/5537 = 28.28% */796 797 /*798 高血压规范管理率（扇形图）：799 查询条件：800 1：管理机构（包含子机构）：北京市西城区新街口社区卫生服务中心801 2：档案完整度：完整802 3：时间范围：2014-04-04到2015-04-03803 4：慢病首诊：做过慢病首诊804 5：慢病随访：4次及以上805 6：居民体检：1次及以上806 7：慢病年度评估：1次及以上807 808 高血压规范管理数:1522809 查询结果：810 COUNT(DISTINCT(EB.EHR_ID)) 811 -------------------------- 812 1522813 */814 SELECT COUNT(DISTINCT(EB.EHR_ID)) 815 FROM EHR_BASE EB, SVC_FIRST_SOAP SFS, SVC_EXAM_1 SE, SVC_ASM_YEAR SAY, SVC_FLW_CHRONIC SFC816 WHERE EB.EHR_ID=SFS.EHR_ID 817 AND EB.EHR_ID=SE.EHR_ID 818 AND EB.EHR_ID=SAY.EHR_ID 819 AND EB.EHR_ID=SFC.EHR_ID 820 AND EB.GR_CHRONIC_DISEASE='1'821 AND EB.CD_HYPERTENSION = '1'822 823 AND SE.EXAM_DATE >= TO_DATE('2014-04-04','YYYY-MM-DD') 824 AND SE.EXAM_DATE <= TO_DATE('2015-04-03','YYYY-MM-DD')825 826 AND SAY.ASM_DATE >= TO_DATE('2014-04-04','YYYY-MM-DD') 827 AND SAY.ASM_DATE <= TO_DATE('2015-04-03','YYYY-MM-DD')828 829 AND SFC.DATE_CREATED >= TO_DATE('2014-04-04','YYYY-MM-DD') 830 AND SFC.DATE_CREATED <= TO_DATE('2015-04-03','YYYY-MM-DD')831 832 AND EB.MNG_ORG_CODE LIKE '0203%'833 AND EB.EHR_INTEGRITY = 1834 AND EB.EHR_ID IN (835 SELECT EHR_ID FROM SVC_EXAM_1836 GROUP BY EHR_ID HAVING COUNT(1) >=1837 )838 AND EB.EHR_ID IN (839 SELECT EHR_ID FROM SVC_ASM_YEAR840 GROUP BY EHR_ID HAVING COUNT(1) >=1841 )842 AND EXISTS (843 SELECT EHR_ID FROM SVC_FLW_CHRONIC844 WHERE SVC_FLW_CHRONIC.EHR_ID = EB.EHR_ID845 GROUP BY EHR_ID HAVING COUNT(1) >=4846 )847 /* 高血压规范管理率 = 高血压规范管理数/高血压人数 = 1522/4786 = 31.80% */848 /*849 糖尿病规范管理率（扇形图）：850 查询条件：851 1：管理机构（包含子机构）：北京市西城区新街口社区卫生服务中心852 2：档案完整度：完整853 3：时间范围：2014-04-04到2015-04-03854 4：慢病首诊：做过慢病首诊855 5：慢病随访：4次及以上856 6：居民体检：1次及以上857 7：慢病年度评估：1次及以上858 糖尿病规范管理数:722859 查询结果：860 COUNT(DISTINCT(EB.EHR_ID))861 --------------------------862 772863 */864 SELECT COUNT(DISTINCT(EB.EHR_ID)) 865 FROM EHR_BASE EB, SVC_FIRST_SOAP SFS, SVC_EXAM_1 SE, SVC_ASM_YEAR SAY, SVC_FLW_CHRONIC SFC866 WHERE EB.EHR_ID=SFS.EHR_ID 867 AND EB.EHR_ID=SE.EHR_ID 868 AND EB.EHR_ID=SAY.EHR_ID 869 AND EB.EHR_ID=SFC.EHR_ID 870 AND EB.GR_CHRONIC_DISEASE='1'871 AND EB.CD_DIABETES_MELLITUS = '1'872 873 AND SE.EXAM_DATE >= TO_DATE('2014-04-04','YYYY-MM-DD') 874 AND SE.EXAM_DATE <= TO_DATE('2015-04-03','YYYY-MM-DD')875 876 AND SAY.ASM_DATE >= TO_DATE('2014-04-04','YYYY-MM-DD') 877 AND SAY.ASM_DATE <= TO_DATE('2015-04-03','YYYY-MM-DD')878 879 AND SFC.DATE_CREATED >= TO_DATE('2014-04-04','YYYY-MM-DD') 880 AND SFC.DATE_CREATED <= TO_DATE('2015-04-03','YYYY-MM-DD')881 882 AND EB.MNG_ORG_CODE LIKE '0203%'883 AND EB.EHR_INTEGRITY = 1884 AND EB.EHR_ID IN (885 SELECT EHR_ID FROM SVC_EXAM_1886 GROUP BY EHR_ID HAVING COUNT(1) >=1887 )888 AND EB.EHR_ID IN (889 SELECT EHR_ID FROM SVC_ASM_YEAR890 GROUP BY EHR_ID HAVING COUNT(1) >=1891 )892 AND EXISTS (893 SELECT EHR_ID FROM SVC_FLW_CHRONIC894 WHERE SVC_FLW_CHRONIC.EHR_ID = EB.EHR_ID895 GROUP BY EHR_ID HAVING COUNT(1) >=4896 )897 /* 糖尿病规范管理率 = 糖尿病规范管理数/糖尿病人数 = 772/2104 = 36.69% */898 /*--------------------------end--规范管理情况---------------------------------------------------------------------*/899 900 /*901 "慢病情况统计"-"规范管理情况"总结：902 1： （1）左边的查询条件中，机构选择、责任医生、选择团队、时间范围会影响到右边所有的扇形图统计数据；903 （2）慢病规范管理复选框会影响到统计图的下半部分统计数据，即慢病规范管理率、高血压规范管理率904 和糖尿病规范管理率；905 （3）老年人规范管理复选框只会影响到老年人规范管理率；906 （4）精神病规范管理复选框只会影响到精神病规范管理率；907 908 */909 /*910 本机构档案统计(扇形图)：911 查询条件：912 1：管理机构（包含子机构）：北京市西城区新街口社区卫生服务中心913 2：时间范围：2014-04-04到2015-04-03914 辖区人口数：108663915 已建档案数：588916 档案使用数：577917 规范档案数：568918 查询结果：919 HEADNUM EHRNUM USENUM NORMNUM 920 ------- ------ ------ ------- 921 108663 588 577 568922 */923 /*924 建档率 = 已建档案数/辖区人口数 = 588/108663 = 0.54%925 使用率 =档案使用数/已建档案数 = 577/588 = 98.13%926 规范率 = 规范档案数/已建档案数 = 568/588 = 96.60%927 */928 SELECT SUM(C.PEOPLE_COUNT) AS HEADNUM,SUM(C.COUNT_已建档案数) AS EHRNUM,SUM(C.COUNT_档案使用数) AS USENUM,SUM(C.COUNT_规范档案数) AS NORMNUM FROM (929 SELECT UO.PEOPLE_COUNT, A.COUNT_已建档案数, B.COUNT_档案使用数, D.COUNT_规范档案数 FROM UMS_ORG UO 930 LEFT JOIN (931 SELECT EB.MNG_ORG_CODE,COUNT(EB.EHR_ID) COUNT_已建档案数932 FROM EHR_BASE EB933 WHERE EB.CANCELLED = '1' 934 AND EB.DEATH = '1' 935 AND EB.MOVED_OUT = '1' 936 AND EB.DATE_CREATED >=TO_DATE('2014-04-04','YYYY-MM-DD')937 AND EB.DATE_CREATED <=TO_DATE('2015-04-03','YYYY-MM-DD')938 AND EB.MNG_ORG_CODE LIKE '0203%'939 GROUP BY EB.MNG_ORG_CODE940 ) A ON UO.ORG_CODE = A.MNG_ORG_CODE941 LEFT JOIN (942 SELECT EB.MNG_ORG_CODE, COUNT(EB.EHR_ID) AS COUNT_档案使用数943 FROM EHR_BASE EB944 WHERE EB.CANCELLED = '1' 945 AND EB.DEATH = '1' 946 AND EB.MOVED_OUT = '1' 947 AND EB.DATE_CREATED >=TO_DATE('2014-04-04','YYYY-MM-DD')948 AND EB.DATE_CREATED <=TO_DATE('2015-04-03','YYYY-MM-DD')949 AND EB.LAST_MNG_DATE !=TO_DATE('1900-01-01','YYYY-MM-DD')950 AND EB.MNG_ORG_CODE LIKE '0203%'951 GROUP BY EB.MNG_ORG_CODE952 ) B ON UO.ORG_CODE = B.MNG_ORG_CODE953 LEFT JOIN (954 SELECT EB.MNG_ORG_CODE,COUNT(EB.EHR_ID) COUNT_规范档案数955 FROM EHR_BASE EB956 WHERE EB.CANCELLED = '1' 957 AND EB.DEATH = '1' 958 AND EB.MOVED_OUT = '1' 959 AND EB.FILE_SPECIFICATION = '1' 960 AND EB.DATE_CREATED >=TO_DATE('2014-04-04','YYYY-MM-DD')961 AND EB.DATE_CREATED <=TO_DATE('2015-04-03','YYYY-MM-DD')962 AND EB.MNG_ORG_CODE LIKE '0203%'963 GROUP BY EB.MNG_ORG_CODE964 ) D ON UO.ORG_CODE = D.MNG_ORG_CODE965 WHERE UO.ORG_CODE LIKE '0203%'966 ) C