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

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

select o.waist,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 ((p.sex=’1′ and o.waist>90) or (p.sex=’2′ and o.waist>’80’))

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

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.waist,o.vstdate

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 ((p.sex=’1′ and o.waist>90) or (p.sex=’2′ and o.waist>’80’))

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 ((p2.sex=’1′ and rs.waist>’90’) or (p2.sex=’2′ and rs.waist>’80’))
order by v.village_id

select c.hn,s1.egfr,(case when s1.egfr between ‘1’ and ‘15.99’ then 1 else 0 end)as 1to15,
(case when s1.egfr between ’16’ and ‘30.99’ then 1 else 0 end)as 16to30,
(case when s1.egfr between ’31’ and ‘45.99’ then 1 else 0 end)as 31to45,
(case when s1.egfr between ’46’ and ‘60.99’ then 1 else 0 end)as 46to60,
(case when s1.egfr between ’61’ and ‘75.99’ then 1 else 0 end)as 61to75,
(case when s1.egfr between ’76’ and ‘90.99’ then 1 else 0 end)as 76to90,
(case when s1.egfr between ’91’ and ‘300.99’ then 1 else 0 end)as 91up
from clinicmember c
left join opdscreen s1 on s1.hn=c.hn
where c.clinic=001 and c.other_chronic_text not like ‘โรคความดัน%’ and s1.egfr<>””
group by c.hn
order by s1.egfr

select c.hn,s1.egfr,(case when s1.egfr between ‘1’ and ‘15.99’ then 1 else 0 end)as 1to15,
(case when s1.egfr between ’16’ and ‘30.99’ then 1 else 0 end)as 16to30,
(case when s1.egfr between ’31’ and ‘45.99’ then 1 else 0 end)as 31to45,
(case when s1.egfr between ’46’ and ‘60.99’ then 1 else 0 end)as 46to60,
(case when s1.egfr between ’61’ and ‘75.99’ then 1 else 0 end)as 61to75,
(case when s1.egfr between ’76’ and ‘90.99’ then 1 else 0 end)as 76to90,
(case when s1.egfr between ’91’ and ‘300.99’ then 1 else 0 end)as 91up
from clinicmember c
left join opdscreen s1 on s1.hn=c.hn
where c.clinic=002 and c.other_chronic_text not like ‘โรคเบาหวาน%’ and s1.egfr<>””
group by c.hn
order by s1.egfr

select c.hn,s1.egfr,c.clinic,c.other_chronic_text,(case when s1.egfr between ‘1’ and ‘15.99’ then 1 else 0 end)as 1to15,
(case when s1.egfr between ’16’ and ‘30.99’ then 1 else 0 end)as 16to30,
(case when s1.egfr between ’31’ and ‘45.99’ then 1 else 0 end)as 31to45,
(case when s1.egfr between ’46’ and ‘60.99’ then 1 else 0 end)as 46to60,
(case when s1.egfr between ’61’ and ‘75.99’ then 1 else 0 end)as 61to75,
(case when s1.egfr between ’76’ and ‘90.99’ then 1 else 0 end)as 76to90,
(case when s1.egfr between ’91’ and ‘300.99’ then 1 else 0 end)as 91up
from clinicmember c
left join opdscreen s1 on s1.hn=c.hn
where c.clinic=001 and c.other_chronic_text like ‘โรคความดัน%’ or c.clinic=002 and c.other_chronic_text like ‘โรคเบาหวาน%’
and s1.egfr<>””
group by c.hn
order by s1.egfr

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

select o.egfr,o.hn,o.vstdate,o.vn,c.clinic,c.other_chronic_text,
(case when o.egfr between ‘1’ and ‘15.99’ then 1 else 0 end)as 1to15,
(case when o.egfr between ’16’ and ‘30.99’ then 1 else 0 end)as 16to30,
(case when o.egfr between ’31’ and ‘45.99’ then 1 else 0 end)as 31to45,
(case when o.egfr between ’46’ and ‘60.99’ then 1 else 0 end)as 46to60,
(case when o.egfr between ’61’ and ‘75.99’ then 1 else 0 end)as 61to75,
(case when o.egfr between ’76’ and ‘90.99’ then 1 else 0 end)as 76to90,
(case when o.egfr between ’91’ and ‘300.99’ then 1 else 0 end)as 91up
from opdscreen o
right join clinicmember c on c.hn=o.hn and c.clinic=001 and c.other_chronic_text not like ‘âä¤ÇÒÁ´Ñ¹%’
where o.egfr<>””
group by o.hn
order by o.vstdate desc

——————————————————————————————————————————–