Query ผลงานการคัดกรองโรคความดันโลหิตสูง เบาหวาน แยกรายเดือน ปี 2559
=============================================================================
SELECT SUBSTRING(house.villcode,7,2) AS moo
,COALESCE(village.villname) AS villname
,COUNT(person.pid) AS pop
,COUNT(screen58.pid) AS target
,ROUND((COUNT(screen58.pid)/COUNT(person.pid))*100,2) AS percent
,SUM(CASE WHEN screen58.screen_date LIKE "2015-10%" THEN 1 ELSE 0 END) AS "oct"
,SUM(CASE WHEN screen58.screen_date LIKE "2015-11%" THEN 1 ELSE 0 END) AS "nov"
,SUM(CASE WHEN screen58.screen_date LIKE "2015-12%" THEN 1 ELSE 0 END) AS "dec"
,SUM(CASE WHEN screen58.screen_date LIKE "2016-01%" THEN 1 ELSE 0 END) AS "jan"
,SUM(CASE WHEN screen58.screen_date LIKE "2016-02%" THEN 1 ELSE 0 END) AS "feb"
,SUM(CASE WHEN screen58.screen_date LIKE "2016-03%" THEN 1 ELSE 0 END) AS "mar"
,SUM(CASE WHEN screen58.screen_date LIKE "2016-04%" THEN 1 ELSE 0 END) AS "apr"
,SUM(CASE WHEN screen58.screen_date LIKE "2016-05%" THEN 1 ELSE 0 END) AS "may"
,SUM(CASE WHEN screen58.screen_date LIKE "2016-06%" THEN 1 ELSE 0 END) AS "jun"
,SUM(CASE WHEN screen58.screen_date LIKE "2016-07%" THEN 1 ELSE 0 END) AS "jul"
,SUM(CASE WHEN screen58.screen_date LIKE "2016-08%" THEN 1 ELSE 0 END) AS "aug"
,SUM(CASE WHEN screen58.screen_date LIKE "2016-09%" THEN 1 ELSE 0 END) AS "sep"
FROM person
INNER JOIN ctitle ON person.prename = ctitle.titlecode
LEFT JOIN persondeath ON person.pcucodeperson = persondeath.pcucodeperson AND person.pid = persondeath.pid
LEFT JOIN house ON house.pcucode = person.pcucodeperson AND house.hcode = person.hcode
LEFT JOIN village ON village.pcucode = house.pcucode AND village.villcode = house.villcode
LEFT JOIN (SELECT ncd_person_ncd_screen.pcucode
,ncd_person_ncd_screen.pid
,ncd_person_ncd_screen.screen_date
FROM ncd_person_ncd_screen
WHERE ncd_person_ncd_screen.screen_date BETWEEN "2015-10-01" AND "2016-09-30") AS screen58
ON person.pcucodeperson = screen58.pcucode AND person.pid = screen58.pid
LEFT JOIN (SELECT personchronic.pid
,personchronic.pcucodeperson
,if(GROUP_CONCAT(personchronic.chroniccode) like"E1%,I1%" or GROUP_CONCAT(personchronic.chroniccode)like"I1%,E1%",1,0) as w1
FROM person
INNER JOIN house ON person.hcode = house.hcode AND person.pcucodeperson = house.pcucode
INNER JOIN village ON house.villcode = village.villcode AND house.pcucode = village.pcucode
LEFT JOIN ctitle ON person.prename = ctitle.titlecode
INNER JOIN personchronic ON person.pcucodeperson = personchronic.pcucodeperson AND person.pid = personchronic.pid
WHERE (personchronic.chroniccode between "E10" AND "E14.9" or personchronic.chroniccode between "I10" and "I15")
AND personchronic.typedischart ="03"
AND right(village.villcode,2)!= "00"
AND person.typelive in (1,3)
AND concat(person.pid,person.pcucodeperson)NOT IN
(select concat(persondeath.pid,persondeath.pcucodeperson)
from persondeath where deaddate <= CURDATE() or deaddate is null)
GROUP BY person.pid
HAVING w1 > 0) AS pchronic
ON person.pcucodeperson = pchronic.pcucodeperson AND person.pid = pchronic.pid
WHERE persondeath.pid IS NULL
AND pchronic.pid IS NULL
AND (YEAR(person.birth)+543) <= "2522"
AND person.typelive NOT IN ("2","4")
GROUP BY house.villcode
WITH ROLLUP;
Query รายชื่อการคัดกรองโรคความดันโลหิตสูง เบาหวาน แยกรายเดือน ปี 2559
=============================================================================
SELECT person.pid,CONCAT_WS('-',substring(idcard,1,1),substring(idcard,2,4),substring(idcard,6,5),substring(idcard,11,2),substring(idcard,13,1))AS idcardd
,concat(ctitle.titlename,' ',person.fname,' ',person.lname) as personname
,CONCAT(DATE_FORMAT(person.birth,'%d-%m-'),DATE_FORMAT(person.birth,'%Y')+543) AS birtht
,YEAR( FROM_DAYS( DATEDIFF( NOW( ) ,person.birth ) ) ) AS age
,person.hnomoi
,person.mumoi
,IFNULL(CONCAT(DATE_FORMAT(screen58.screen_date,'%d-%m-'),DATE_FORMAT(screen58.screen_date,'%Y')+543),'') AS NcdScreen58
FROM person
INNER JOIN ctitle ON person.prename = ctitle.titlecode
LEFT JOIN persondeath ON person.pcucodeperson = persondeath.pcucodeperson AND person.pid = persondeath.pid
LEFT JOIN house ON house.pcucode = person.pcucodeperson AND house.hcode = person.hcode
LEFT JOIN village ON village.pcucode = house.pcucode AND village.villcode = house.villcode
LEFT JOIN (SELECT ncd_person_ncd_screen.pcucode
,ncd_person_ncd_screen.pid
,ncd_person_ncd_screen.screen_date
FROM ncd_person_ncd_screen
WHERE ncd_person_ncd_screen.screen_date BETWEEN '2015-10-01' AND '2016-09-30') AS screen58
ON person.pcucodeperson = screen58.pcucode AND person.pid = screen58.pid
LEFT JOIN (SELECT
personchronic.pid
,personchronic.pcucodeperson
,IF(GROUP_CONCAT(personchronic.chroniccode) LIKE'E1%,I1%' OR GROUP_CONCAT(personchronic.chroniccode) LIKE 'I1%,E1%',1,0) AS w1
FROM person
INNER JOIN house ON person.hcode = house.hcode AND person.pcucodeperson = house.pcucode
INNER JOIN village ON house.villcode = village.villcode AND house.pcucode = village.pcucode
LEFT JOIN ctitle ON person.prename = ctitle.titlecode
INNER JOIN personchronic ON person.pcucodeperson = personchronic.pcucodeperson AND person.pid = personchronic.pid
WHERE (personchronic.chroniccode BETWEEN 'E10' AND 'E14.9' OR personchronic.chroniccode BETWEEN 'I10' AND 'I15')
AND personchronic.typedischart ='03'
AND RIGHT(village.villcode,2)!= '00'
AND person.typelive IN (1,3)
AND concat(person.pid,person.pcucodeperson)NOT IN
(SELECT concat(persondeath.pid,persondeath.pcucodeperson)
FROM persondeath WHERE deaddate <= CURDATE() OR deaddate IS NULL)
GROUP BY person.pid
HAVING w1 > 0) AS pchronic
ON person.pcucodeperson = pchronic.pcucodeperson AND person.pid = pchronic.pid
WHERE persondeath.pid IS NULL
AND pchronic.pid IS NULL
AND (YEAR(person.birth)+543) <= '2522'
AND person.typelive NOT IN ('2','4')
ORDER BY village.villcode,person.hnomoi*1 ASC;
การใช้ยาอย่างสมเหตุผล URI/AGE 12/09/2559
=========================
SELECT
COUNT(atbuse.pid) AS 'ผู้ป่วยใช้anitibiotic'
,COUNT(person.pid) AS 'ผู้ป่วยทั้งหมด'
,ROUND((COUNT(atbuse.pid)/COUNT(person.pid))*100,2) AS 'ร้อยละ'
FROM person
LEFT JOIN visit ON person.pid = visit.pid
LEFT JOIN visitdiag ON visit.visitno = visitdiag.visitno
LEFT JOIN cdisease ON visitdiag.diagcode = cdisease.diseasecode
LEFT JOIN persondeath ON person.pcucodeperson = persondeath.pcucodeperson AND person.pid = persondeath.pid
LEFT JOIN (SELECT person.pcucodeperson,person.pid,visit.visitno FROM cdrug
INNER JOIN visitdrug ON cdrug.drugcode = visitdrug.drugcode
INNER JOIN visit ON visitdrug.visitno = visit.visitno
INNER JOIN person ON visit.pid = person.pid
INNER JOIN visitdiag ON visit.visitno = visitdiag.visitno
INNER JOIN cdisease ON visitdiag.diagcode = cdisease.diseasecode
LEFT JOIN persondeath ON person.pcucodeperson = persondeath.pcucodeperson AND person.pid = persondeath.pid
WHERE cdrug.antibio = 1
AND visitdiag.diagcode IN ('A00','a00.1','a00.9','A02.0','A03.0','A03.1','A03.2','A03.3','A03.8','A03.9',
'A04.0','A04.1','A04.2','A04.3','A04.4','A04.5','A04.6','A04.7','A04.8','A04.9',
'A05.0','A05.3','A05.4','A05.9','A08.0','A08.1','A08.2','A08.3','A08.4','A08.5','A09.0','A09.9','K52.1','K52.8','K52.9')
AND dxtype = '01'
AND persondeath.pid IS NULL
AND visitdate BETWEEN '2015-10-01' AND '2016-09-30') AS atbuse
ON visit.visitno = atbuse.visitno
WHERE visitdiag.diagcode IN ('A00','a00.1','a00.9','A02.0','A03.0','A03.1','A03.2','A03.3','A03.8','A03.9',
'A04.0','A04.1','A04.2','A04.3','A04.4','A04.5','A04.6','A04.7','A04.8','A04.9',
'A05.0','A05.3','A05.4','A05.9','A08.0','A08.1','A08.2','A08.3','A08.4','A08.5','A09.0','A09.9','K52.1','K52.8','K52.9')
AND dxtype = '01'
AND visitdate BETWEEN '2015-10-01' AND '2016-03-31'
AND persondeath.pid IS NULL
AND person.typelive in (0,1,3)
=================================================================
SELECT
COUNT(atbuse.pid) AS 'ผู้ป่วยใช้anitibiotic'
,COUNT(person.pid) AS 'ผู้ป่วยทั้งหมด'
,ROUND((COUNT(atbuse.pid)/COUNT(person.pid))*100,2) AS 'ร้อยละ'
FROM person
LEFT JOIN visit ON person.pid = visit.pid
LEFT JOIN visitdiag ON visit.visitno = visitdiag.visitno
LEFT JOIN cdisease ON visitdiag.diagcode = cdisease.diseasecode
LEFT JOIN persondeath ON person.pcucodeperson = persondeath.pcucodeperson AND person.pid = persondeath.pid
LEFT JOIN (SELECT person.pcucodeperson,person.pid,visit.visitno FROM cdrug
INNER JOIN visitdrug ON cdrug.drugcode = visitdrug.drugcode
INNER JOIN visit ON visitdrug.visitno = visit.visitno
INNER JOIN person ON visit.pid = person.pid
INNER JOIN visitdiag ON visit.visitno = visitdiag.visitno
INNER JOIN cdisease ON visitdiag.diagcode = cdisease.diseasecode
LEFT JOIN persondeath ON person.pcucodeperson = persondeath.pcucodeperson AND person.pid = persondeath.pid
WHERE cdrug.antibio = 1
AND visitdiag.diagcode IN ('B05.3','H65.0','H65.1','H65.9','H66.0','H66.4','H66.9','H67.0',
'H67.1','H67.8','H72.0','H72.1','H72.2','H72.8','H72.9',
'J00','J01.0','J01.1','J01.2','J01.3','J01.4','J01.8','J01.9','J02.0','J02.9','J03.0','J03.8','J03.9',
'J04.0','J04.1','J04.2','J05.0','J05.1','J06.0','J06.8','J06.9','J10.1','J11.1','J20.0','J20.1','J20.2','J20.3','J20.4','J20.5','J20.6','J20.7',
'J20.8','J20.9','J21.0','J21.8','J21.9')
AND dxtype = '01'
AND persondeath.pid IS NULL
AND visitdate BETWEEN '2015-10-01' AND '2016-09-30') AS atbuse
ON visit.visitno = atbuse.visitno
WHERE visitdiag.diagcode IN ('B05.3','H65.0','H65.1','H65.9','H66.0','H66.4','H66.9','H67.0',
'H67.1','H67.8','H72.0','H72.1','H72.2','H72.8','H72.9',
'J00','J01.0','J01.1','J01.2','J01.3','J01.4','J01.8','J01.9','J02.0','J02.9','J03.0','J03.8','J03.9',
'J04.0','J04.1','J04.2','J05.0','J05.1','J06.0','J06.8','J06.9','J10.1','J11.1','J20.0','J20.1','J20.2','J20.3','J20.4','J20.5','J20.6','J20.7',
'J20.8','J20.9','J21.0','J21.8','J21.9')
AND dxtype = '01'
AND visitdate BETWEEN '2015-10-01' AND '2016-03-31'
AND persondeath.pid IS NULL
AND person.typelive in (0,1,3)
