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

select o.bmi,p.cid,p.sex,p.patient_hn,p.age_y,
(case when p.sex=’1′ then 1 else 0 end)as M,
(case when p.sex=’2′ then 1 else 0 end)as W

from person p
right join patient p2 on p2.hn=p.patient_hn
right join opdscreen o on o.hn=p2.hn
where p.age_y >’14’ and (p2.chwpart=’32’ and p2.amppart=’04’ and p2.tmbpart=’01’)
and o.bmi between ‘18.5’and ‘22.9’

group by p.patient_hn
order by o.vstdate desc

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

select p.cid,p.sex,p.patient_hn,p.age_y,concat(p.pname,p.fname,’ ‘,p.lname)as name,concat(p2.addrpart,’ M ‘,p2.moopart,’ ‘,t.full_name)as address,
(case when p.sex=’1′ then 1 else 0 end)as M,
(case when p.sex=’2’ then 1 else 0 end)as W,o.bmi

from person p
right join patient p2 on p2.hn=p.patient_hn
right join opdscreen o on o.hn=p2.hn
right join thaiaddress t on t.chwpart=p2.chwpart and t.amppart=p2.amppart and t.tmbpart=p2.tmbpart
where o.vstdate between ‘2013-10-01’ and ‘2014-01-01′ and p.age_y >’14’ and (p2.chwpart=’32’ and p2.amppart=’04’ and p2.tmbpart=’01’)
and o.bmi between ‘18.5’and ‘22.9’

group by p.patient_hn
order by o.vstdate desc

————————————————————————————————————————————

select rs.bmi,rs.waist,p2.sex,p2.cid,p2.patient_hn,rs.screen_date,concat(p2.pname,p2.fname,’ ‘,p2.lname) as name,p2.age_y,
(h1.address)as addpart,concat(v.village_name,’ หมู่ ‘,v.village_moo) as address,
(case when p2.sex=’1′ then 1 else 0 end)as M,
(case when p2.sex=’2’ then 1 else 0 end)as W,
s1.person_dm_screen_status_name, s2.person_ht_screen_status_name ,
s3.person_stroke_screen_status_name , s4.person_obesity_screen_status_name,
d1.person_dmht_manage_type_name ,
p2.death,p2.death_date,h2.house_regist_type_name,p1.*

from person_dmht_screen_summary p1
left outer join person p2 on p2.person_id = p1.person_id
left outer join house_regist_type h2 on h2.house_regist_type_id = p2.house_regist_type_id
left outer join house h1 on h1.house_id = p2.house_id
left outer join village v on v.village_id = h1.village_id
left outer join person_dm_screen_status s1 on s1.person_dm_screen_status_id = p1.person_dm_screen_status_id
left outer join person_ht_screen_status s2 on s2.person_ht_screen_status_id = p1.person_ht_screen_status_id
left outer join person_stroke_screen_status s3 on s3.person_stroke_screen_status_id = p1.person_stroke_screen_status_id
left outer join person_obesity_screen_status s4 on s4.person_obesity_screen_status_id = p1.person_obesity_screen_status_id
left outer join person_dmht_manage_type d1 on d1.person_dmht_manage_type_id = p1.person_dmht_manage_type_id
left outer join person_dmht_risk_screen_head rs on rs.person_dmht_screen_summary_id=p1.person_dmht_screen_summary_id
where rs.screen_date between ‘2013-10-01’ and ‘2014-01-01′ and p1.bdg_year = 2557 and p1.status_active=’Y’ and p2.death=”N’
and p2.age_y>’14’ and (rs.bmi between ‘18.5’ and ‘22.9’)
order by v.village_id

0

0