จมน้ำจำแนกไทย/ต่างชาติ
ID | 777 |
---|---|
Parent ID | 1 |
Table Name | s_drown_all |
Title | จมน้ำจำแนกไทย/ต่างชาติ |
Description | |
Script | #SQL_OPTIONS# PROVIDERS=1 PROVIDER1=43STD PROVIDER1_VALIDATE_TABLES=accident,admission,anc,appointment SCRIPT_FLOW=SQL #SQL_OPTIONS# #PROVIDER1_SQL# SET @b_year:=(SELECT yearprocess FROM pk_byear LIMIT 1); SET @start_d:=concat(@b_year-1,'1001'); SET @end_d:=concat(@b_year,'0930'); DROP TABLE IF EXISTS tmp_drown; CREATE TABLE IF NOT EXISTS tmp_drown select a.*,concat(b.name,' ',b.lname),b.nation,b.labor,b.birth,timestampdiff(YEAR,b.BIRTH,a.DATE_SERV) as AGE_Y,b.vhid from (SELECT o.HOSPCODE,o.PID,o.seq,o.CID,o.DATE_SERV,o.DIAGCODE,s.typeout,'opd' FROM tmp_diag_opd o INNER JOIN tmp_service s ON o.hospcode=s.hospcode AND o.pid=s.pid AND o.seq=s.seq AND o.date_serv=s.date_serv WHERE SUBSTR(DIAGCODE ,1,3) BETWEEN 'W65' AND 'W74' AND o.DATE_SERV BETWEEN @start_d AND @end_d union select i.HOSPCODE,i.PID,a.an,i.CID,DATE_FORMAT(i.datetime_disch,'%Y%m%d'),i.DIAGCODE,a.DISCHTYPE ,'ipd' FROM tmp_admission a INNER JOIN tmp_diag_ipd i ON i.an=a.an WHERE SUBSTR(DIAGCODE ,1,3) BETWEEN 'W65' AND 'W74' AND DATE_FORMAT(i.datetime_disch,'%Y%m%d') BETWEEN @start_d AND @end_d) a INNER JOIN t_person_db b ON a.hospcode = b.hospcode and a.cid = b.cid group by a.cid,a.date_serv,a.typeout; SELECT a.hospcode,a.vhid,@b_year + 543 AS b_year,count(a.CID) AS person, COUNT(IF(a.NATION = '099',CONCAT(a.HOSPCODE,a.PID,a.typeout),NULL)) AS 'thai', COUNT(IF(a.NATION = '099' AND a.opd = 'opd'ANd a.typeout IN ('1','2','3'),CONCAT(a.HOSPCODE,a.PID,a.typeout),NULL)) AS 'drown_opd_thai', COUNT(IF(a.NATION = '099' AND a.opd = 'ipd' AND a.typeout NOT IN ('8','9'),CONCAT(a.HOSPCODE,a.PID,a.typeout),NULL)) AS 'drown_ipd_thai', COUNT(IF(a.NATION = '099' AND a.opd = 'opd' AND a.typeout IN ('4','5','6'),CONCAT(a.HOSPCODE,a.PID,a.typeout),NULL)) AS 'death_opd_thai', COUNT(IF(a.NATION = '099' AND a.opd = 'ipd' AND a.typeout IN ('8','9'),CONCAT(a.HOSPCODE,a.PID,a.typeout),NULL)) AS 'death_ipd_thai', COUNT(IF(a.NATION <> '099',CONCAT(a.HOSPCODE,a.PID,a.typeout),NULL)) AS 'foreign', COUNT(IF(a.NATION <> '099' AND a.opd = 'opd' AND a.typeout IN ('1','2','3'),CONCAT(a.HOSPCODE,a.PID,a.typeout),NULL)) AS 'drown_opd_foreign', COUNT(IF(a.NATION <> '099' AND a.opd = 'ipd' AND a.typeout NOT IN ('8','9'),CONCAT(a.HOSPCODE,a.PID,a.typeout),NULL)) AS 'drown_ipd_foreign', COUNT(IF(a.NATION <> '099' AND a.opd = 'opd' AND a.typeout IN ('4','5','6'),CONCAT(a.HOSPCODE,a.PID,a.typeout),NULL)) AS 'death_opd_foreign', COUNT(IF(a.NATION <> '099'AND a.opd = 'ipd' AND a.typeout IN ('8','9'),CONCAT(a.HOSPCODE,a.PID,a.typeout),NULL)) AS 'death_ipd_foreign' FROM tmp_drown a group by a.HOSPCODE,a.vhid; #PROVIDER1_SQL# |
Script Cron | |
Force Script Cron | 0 |
Active | 1 |
Client Office Type | |
Create Date | 2017-08-30 01:56:38 |
Last Update | 2017-08-30 01:56:38 |
- Home
- About
- Privacy Policy
- Terms of Use
- Contact Us
© 2015. All Rights Reserved.