Pneumonia

SET @start_date = '25661001';
SET @end_date = '25670930';
 
SELECT
 
p.hn, concat(p.prename,p.name,' ',p.surname) as 'ชื่อ-สกุล'
,TRUNCATE((SELECT DATEDIFF(CONCAT((DATE_FORMAT(ipd.datedsc,"%Y")-543),"-",DATE_FORMAT(ipd.datedsc,"%m-%d")), STR_TO_DATE(p.birthdate, '%Y-%m-%d'))/365),0) AS 'อายุ ณ วันวินิจฉัย'
,DATE_FORMAT(ipd.dateadm,"%d-%m-%Y") as 'Admit'
,DATE_FORMAT(ipd.datedsc,"%d-%m-%Y") as 'DC'
,p.address as 'บ้านเลขที่',p.moo as 'หมู่ที่',ad.name as 'ตำบล', SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(ad.full_name,' ',2),' ',-1), '.', -1) as 'amphur',SUBSTRING_INDEX(SUBSTRING_INDEX(ad.full_name,' ',-1),'.',-1) as 'province'
 
from irf
 
INNER JOIN ipd ON irf.an = ipd.an
INNER JOIN idx ON ipd.an = idx.an
INNER JOIN pat ON ipd.an = pat.an
INNER JOIN hos_pt p ON p.hn = pat.hn
LEFT JOIN hos_code_thaiaddress ad on concat(p.province,p.amphur,p.tambon) =
ad.addressid
 
inner join iop on pat.an = iop.an
WHERE irf.refertype = '2'
AND idx.dxtype = '1'
AND ipd.datedsc BETWEEN @start_date AND @end_date
 
AND TRUNCATE((SELECT DATEDIFF(CONCAT((DATE_FORMAT(ipd.dateadm,"%Y")-543),"-",DATE_FORMAT(ipd.dateadm,"%m-%d")), STR_TO_DATE(CONCAT((DATE_FORMAT(pat.dob,"%Y")-543),"-",DATE_FORMAT(pat.dob,"%m-%d")), '%Y-%m-%d'))/365),0) >= 15
 
AND idx.diag BETWEEN 'J12' AND 'J18'
 
AND iop.oper IN ('9605','9604')
 
-- GROUP BY idx.diag
-- ORDER BY count(idx.diag) DESC

SELECT
COUNT(DISTINCT(v.an)) as 'visit'
,COUNT(DISTINCT(v.hn)) as 'people'
FROM ipd v
INNER JOIN idx d ON v.an = d.an
INNER JOIN pat ON v.an = pat.an
WHERE v.datedsc BETWEEN '25661001' AND '25670930'
and d.diag BETWEEN 'J12' AND 'J18'
and d.dxtype = '1'