JHCIS Query ข้อมูลระบาดวิทยา

SELECT CONCAT(t.titlename,p.fname,' ',p.lname) AS pname,h.hno,h.villcode,h.xgis,h.ygis,vd.sickdatestart,dc.group506name FROM house AS h Inner Join person AS p ON h.pcucode = p.pcucodeperson AND h.hcode = p.hcode Inner Join visit AS v ON p.pcucodeperson = v.pcucodeperson AND p.pid = v.pid Inner Join visitdiag506address AS vd ON v.pcucode = vd.pcucode AND v.visitno = vd.visitno Inner Join cdisease AS d ON vd.diagcode = d.diseasecode Inner Join cdisease506 AS dc ON d.code506 = dc.group506code Inner Join ctitle AS t ON p.prename = t.titlecode WHERE dc.group506code = '02' AND vd.sickdatestart BETWEEN '2002/10/29' AND '2011/10/29' AND SUBSTRING(h.villcode,7,2) <> '00'
ORDER BY h.villcode,h.hno

JHCIS Query ข้อมูลผู้ป่วยเรื้อรัง

SELECT
p.prename,CONCAT(p.fname,' ',p.lname) AS pname,h.hno,h.villcode,h.xgis,h.ygis,
p.idcard,p.pcucodeperson,p.pid
FROM
house AS h
Inner Join person AS p ON h.pcucode = p.pcucodeperson AND h.hcode = p.hcode Inner Join personchronic AS pc ON p.pcucodeperson = pc.pcucodeperson AND p.pid = pc.pid
Inner Join cdisease AS d ON pc.chroniccode = d.diseasecode
Inner Join cdiseasechronic AS dc ON d.codechronic = dc.groupcode
WHERE
p.dischargetype NOT IN ('1', '3') AND
dc.groupcode = ''
AND SUBSTRING(h.villcode,7,2) <> '00'
AND pc.typedischart NOT IN ('01', '02','07','10')
GROUP BY p.pcucodeperson,p.pid;

อธิบาย
p.dischargetype NOT IN ('1', '3') 1 = ตาย , 2 = ย้าย , 3 = สาบสูญ ,9 =ไม่จำหน่าย
pc.typedischart NOT IN ('01', '02','07','10') 01 = หาย , 02 = ตาย , 03 = ยังรักษาอยู่ , 04 = ไม่ทราบ(ไม่มีข้อมูล) 05 = รอจำหน่าย/เฝ้าระวัง, 06=ขาดการรักษาไม่มาติดต่ออีก (ทราบว่าขาดการรักษา), 07=ครบการรักษา, 08=โรคอยู่ในภาวะสงบ (inactive)ไม่มีความจำเป็นต้องรักษา, 09=ปฏิเสธการรักษา, 10 = ออกจากพื้นที่

code SQL Person ของ JHCIS ที่ใช้สำหรับ ออก 18 แฟ้ม

เป็นตัวสึแดงเป็นรหัสสถานพยาบาลของเราก่อนน๊ะครับ
เครื่องมือสำหรับ run sql เหล่านี้มีหลายตัว เช่น Mysqlfont, PhpMyAdmin, Aquadata studio,EMS sql Manager,Mysql Admin tool ฯลฯ
SELECT DISTINCT D.pcucodeperson, D.idcard, D.pid, D.hcode, D.prename, D.fname, D.lname, D.sex, IF(D.birth='' OR D.birth='0000-00-00','',D.birth) AS birth, D.roadmoi, D.hnomoi, D.mumoi, D.subdistcodemoi, D.distcodemoi, D.provcodemoi, D.marystatus, D.occupa, IF(D.origin IS NULL OR TRIM(D.origin)='','',D.origin) AS origin, IF(D.nation IS NULL OR TRIM(D.nation)='','',D.nation) AS nation, D.religion, D.educate, D.familyposition, D.fatherid, D.motherid, D.mateid, IF(D.datein IS NULL OR D.datein='' OR D.datein='0000-00-00','',D.datein) AS movein, D.dischargetype AS dischar, IF(D.dischargedate IS NULL OR TRIM(D.dischargedate)='' OR D.dischargedate='0000-00-00','',D.dischargedate) AS ddisch, D.bloodgroup, D.intercode, D.typelive, IF(H.villcode IS NOT NULL,H.villcode,'') AS villcode, IF(CN.mapnation IS NOT NULL AND TRIM(CN.mapnation)<>'',CN.mapnation,'') AS mapnation, IF(CR.mapnation IS NOT NULL AND TRIM(CR.mapnation)<>'',CR.mapnation,'') AS maporigin, IF(D.dateupdate IS NULL OR TRIM(D.dateupdate)='' OR D.dateupdate='0000-00-00', CONCAT(DATE_FORMAT(CURDATE(),'%Y-%m-%d'),' ',TIME_FORMAT(CURTIME(),'%H:%i:%s')), D.dateupdate) AS d_update FROM person D LEFT JOIN house H ON (D.pcucodeperson=H.pcucode AND D.hcode=H.hcode) LEFT JOIN cnation CN ON (D.nation=CN.nationcode) LEFT JOIN cnation CR ON (D.origin=CR.nationcode) WHERE SUBSTRING(H.villcode,7,2)<>'00' AND D.pcucodeperson='09558' UNION SELECT DISTINCT D.pcucodeperson, D.idcard, D.pid, D.hcode, D.prename, D.fname, D.lname, D.sex, IF(D.birth='' OR D.birth='0000-00-00','',D.birth) AS birth, D.roadmoi, D.hnomoi, D.mumoi, D.subdistcodemoi, D.distcodemoi, D.provcodemoi, D.marystatus, D.occupa, IF(D.origin IS NULL OR TRIM(D.origin)='','',D.origin) AS origin, IF(D.nation IS NULL OR TRIM(D.nation)='','',D.nation) AS nation, D.religion, D.educate, D.familyposition, D.fatherid, D.motherid, D.mateid, IF(D.datein IS NULL OR D.datein='' OR D.datein='0000-00-00','',D.datein) AS movein, D.dischargetype AS dischar, IF(D.dischargedate IS NULL OR TRIM(D.dischargedate)='' OR D.dischargedate='0000-00-00','',D.dischargedate) AS ddisch, D.bloodgroup, D.intercode, D.typelive, IF(H.villcode IS NOT NULL,H.villcode,'') AS villcode, IF(CN.mapnation IS NOT NULL AND TRIM(CN.mapnation)<>'',CN.mapnation,'') AS mapnation, IF(CR.mapnation IS NOT NULL AND TRIM(CR.mapnation)<>'',CR.mapnation,'') AS maporigin, IF(D.dateupdate IS NULL OR TRIM(D.dateupdate)='' OR D.dateupdate='0000-00-00', CONCAT(DATE_FORMAT(CURDATE(),'%Y-%m-%d'),' ',TIME_FORMAT(CURTIME(),'%H:%i:%s')), D.dateupdate) AS d_update FROM person D INNER JOIN visit V ON (D.pcucodeperson=V.pcucodeperson AND D.pid=V.pid) LEFT JOIN house H ON (D.pcucodeperson=H.pcucode AND D.hcode=H.hcode) LEFT JOIN cnation CN ON (D.nation=CN.nationcode) LEFT JOIN cnation CR ON (D.origin=CR.nationcode) WHERE SUBSTRING(H.villcode,7,2)='00' AND D.pcucodeperson='09558'