sql ความดันโลหิตสูง

[uploadfile]

HT ทั้งหมด

SELECT DISTINCT
personchronic.pcucodeperson,
personchronic.pid,
personchronic.chroniccode,
personchronic.datefirstdiag
FROM
personchronic
WHERE
personchronic.chroniccode IN ('I10', 'I15.0', 'I15.1', 'I15.2', 'I15.8', 'I15.9')
ORDER BY
personchronic.pid  ASC

[uploadfile]

ณ เดือน ตุลาคม 2556

SELECT DISTINCT
personchronic.pcucodeperson,
personchronic.pid,
personchronic.chroniccode,
personchronic.datefirstdiag
FROM
personchronic
WHERE
personchronic.chroniccode IN ('I10', 'I15.0', 'I15.1', 'I15.2', 'I15.8', 'I15.9') AND
personchronic.datefirstdiag < '2013-10-01'
ORDER BY
personchronic.pid  ASC

[uploadfile]

SELECT
personchronic.pcucodeperson,
personchronic.pid,
v.diagcode,
v.dxtype
FROM
personchronic
INNER JOIN
(SELECT visit.pcucodeperson,visit.pid,visitdiag.diagcode,visitdiag.dxtype FROM visit,visitdiag WHERE
visitdiag.diagcode IN ('I10', 'I15.0', 'I15.1', 'I15.2', 'I15.8', 'I15.9') AND
visit.pcucode = visitdiag.pcucode AND visit.visitno = visitdiag.visitno AND visitdiag.dxtype IN ('01','04') AND
visit.visitdate BETWEEN '2013-10-01' AND '2013-10-31'
)as v
ON personchronic.pcucodeperson = v.pcucodeperson AND personchronic.pid = v.pid
WHERE
personchronic.chroniccode IN ('I10', 'I15.0', 'I15.1', 'I15.2', 'I15.8', 'I15.9') AND
personchronic.datefirstdiag < '2013-10-01'
ORDER BY
personchronic.pid  ASC

[uploadfile]

SELECT
CONCAT(ctitle.titlename,person.fname,' ',person.lname)AS 'ชื่อ-สกุล',
GetAgeYearNum(person.birth,CURRENT_DATE)AS 'อายุ',
CONCAT(person.hnomoi,' ','หมู่',person.mumoi) AS 'ที่อยู่',
CONCAT(MAX(CASE WHEN MONTH(visit.visitdate)=10 THEN substr(visit.pressure,1,INSTR(visit.pressure,'/')-1) ELSE '' END) ,'/',
MAX(CASE WHEN MONTH(visit.visitdate)=10 THEN substr(visit.pressure,INSTR(visit.pressure,'/')+1,LENGTH(visit.pressure)-INSTR(visit.pressure,'/')) ELSE '' END) ) as 'ต.ค.',
MAX(CASE WHEN MONTH(visit.visitdate)=11 THEN substr(visit.pressure,1,INSTR(visit.pressure,'/')-1) ELSE '' END) AS 'พ.ย.',
MAX(CASE WHEN MONTH(visit.visitdate)=12 THEN substr(visit.pressure,1,INSTR(visit.pressure,'/')-1) ELSE '' END) AS 'ธ.ค.',
MAX(CASE WHEN MONTH(visit.visitdate)=1 THEN substr(visit.pressure,1,INSTR(visit.pressure,'/')-1) ELSE '' END) AS 'ม.ค.',
MAX(CASE WHEN MONTH(visit.visitdate)=2 THEN substr(visit.pressure,1,INSTR(visit.pressure,'/')-1) ELSE '' END) AS 'ก.พ.',
MAX(CASE WHEN MONTH(visit.visitdate)=3 THEN substr(visit.pressure,1,INSTR(visit.pressure,'/')-1) ELSE '' END) AS 'มี.ค.',
MAX(CASE WHEN MONTH(visit.visitdate)=4 THEN substr(visit.pressure,1,INSTR(visit.pressure,'/')-1) ELSE '' END) AS 'เม.ย.',
MAX(CASE WHEN MONTH(visit.visitdate)=5 THEN substr(visit.pressure,1,INSTR(visit.pressure,'/')-1) ELSE '' END) AS 'พ.ค.',
MAX(CASE WHEN MONTH(visit.visitdate)=6 THEN substr(visit.pressure,1,INSTR(visit.pressure,'/')-1) ELSE '' END) AS 'มิ.ย.',
MAX(CASE WHEN MONTH(visit.visitdate)=7 THEN substr(visit.pressure,1,INSTR(visit.pressure,'/')-1) ELSE '' END) AS 'ก.ค.',
MAX(CASE WHEN MONTH(visit.visitdate)=8 THEN substr(visit.pressure,1,INSTR(visit.pressure,'/')-1) ELSE '' END) AS 'ส.ค.',
MAX(CASE WHEN MONTH(visit.visitdate)=9 THEN substr(visit.pressure,1,INSTR(visit.pressure,'/')-1) ELSE '' END) AS 'ก.ย.'
FROM person
INNER JOIN ctitle ON person.prename = ctitle.titlecode
INNER JOIN personchronic ON person.pid=personchronic.pid
INNER JOIN cdisease ON personchronic.chroniccode=cdisease.diseasecode
INNER JOIN house ON person.hcode = house.hcode AND person.pcucodeperson = house.pcucode
INNER JOIN visit ON person.pcucodeperson = visit.pcucodeperson
AND person.pid = visit.pid
INNER JOIN village ON house.villcode = village.villcode AND house.pcucode = village.pcucode
WHERE visit.visitdate BETWEEN '2013-10-01' AND '2014-09-30'
AND SUBSTRING(house.villcode,7,2)<> '00' AND substr(visit.pressure,1,INSTR(visit.pressure,'/')-1) BETWEEN 130 AND 250 AND substr(visit.pressure,INSTR(visit.pressure,'/')+1,LENGTH(visit.pressure)-INSTR(visit.pressure,'/')) BETWEEN 90 AND 250
AND personchronic.chroniccode BETWEEN 'E10' AND 'E14.9'
GROUP BY personchronic.pid
ORDER BY village.villno