• Home
  • My Scripts
  • จมน้ำจำแนกไทย/ต่างชาติ

จมน้ำจำแนกไทย/ต่างชาติ

Update Delete

ID777
Parent ID1
Table Names_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 Cron0
Active1
Client Office Type
Create Date2017-08-30 01:56:38
Last Update2017-08-30 01:56:38