โรงพยาบาลจอมพระ

set @d1 = ‘2015-05-31’;
set @d2 = ‘2016-10-01’;

SELECT * FROM

(select p.patient_hn as hn, p.cid, concat(p.pname, p.fname ,” “, p.lname) as pt_name
, TIMESTAMPDIFF(year,p.birthdate,now()) as age,
p.nationality,p.person_discharge_id,p.death,p.death_date,p.house_regist_type_id,m.clinic_member_status_id

,if(p.sex =1,”ชาย”,”หญิง”) as sex ,h.address, v1.village_moo, v1.village_name
, “NOT PASS” as group1,p.patient_hn,ps2.icd10

,(select if(bps between ‘1’ and ‘140’ and bpd between ‘1’ and ’90’,1,0) as poin
from opdscreen o1 WHERE o1.hn = v.hn and vstdate between @d1 and @d2
ORDER BY vstdate DESC limit 0,1) as dtx1

,(select vstdate
from opdscreen o1d WHERE o1d.hn = v.hn and vstdate between @d1 and @d2
ORDER BY vstdate DESC limit 0,1) as dtx1d
,(select bps
from opdscreen o1a WHERE o1a.hn = v.hn and vstdate between @d1 and @d2
ORDER BY vstdate DESC limit 0,1) as dtx1a
,(select bpd
from opdscreen o1b WHERE o1b.hn = v.hn and vstdate between @d1 and @d2
ORDER BY vstdate DESC limit 0,1) as dtx1b
,(select if(bps between ‘1’ and ‘140’ and bpd between ‘1’ and ’90’,1,0) as poin
from opdscreen o2 WHERE o2.hn = v.hn and vstdate between @d1 and @d2
ORDER BY vstdate DESC limit 1,1) as dtx2

,(select vstdate
from opdscreen o2d WHERE o2d.hn = v.hn and vstdate between @d1 and @d2
ORDER BY vstdate DESC limit 1,1) as dtx2d
,(select bps
from opdscreen o2a WHERE o2a.hn = v.hn and vstdate between @d1 and @d2
ORDER BY vstdate DESC limit 1,1) as dtx2a
,(select bpd
from opdscreen o2b WHERE o2b.hn = v.hn and vstdate between @d1 and @d2
ORDER BY vstdate DESC limit 1,1) as dtx2b

from vn_stat v
left join person p on p.patient_hn = v.hn
LEFT JOIN house h on h.house_id = p.house_id
LEFT JOIN village v1 on v1.village_id = p.village_id
left join clinic_persist_icd ps on ps.hn=v.hn
left join clinic_persist_icd ps2 on ps2.hn=ps.hn and ps2.icd10 between ‘e10′ and’e149’
left join clinicmember m on m.hn=ps.hn

where (v.vstdate between @d1 and @d2)
and /* v.hn in (select hn from clinicmember where clinic=”002″) */
((ps.icd10 between ‘i10’ and ‘i159′ or ps.icd10=’i674′ or ps.icd10=’H350′)
and (p.death=’N’) and (p.house_regist_type_id in (‘1′,’3′))and (m.clinic_member_status_id =’3’))
and (ps2.icd10 is null)
ORDER BY v.hn ) t1

WHERE t1.dtx1 =1 and t1.dtx2= 1
GROUP BY t1.hn