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

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

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

select * from person_visit    where person_visit_type_id=2  and visit_staff not in (‘pcu6′,’pha15’)

select p.pttype,t.hipdata_code,o.hba1c,o.hn,o.vstdate,o.vn,s.pdx,s.dx0,s.dx1,s.dx2,s.dx3,s.dx4,s.dx5
from opdscreen o
join patient p on p.hn=o.hn
join pttype t on t.pttype=p.pttype
join vn_stat s on s.vn=o.vn
where (o.vstdate between ‘2013-10-01’ and ‘2014-06-31′)
and o.hba1c<7
and (t.hipdata_code=’UCS’)
and ((s.pdx like “e%”) or (s.dx0 like “e%”) or (s.dx2 like “e%”) or (s.dx3 like “e%”) or (s.dx4 like “e%”) or (s.dx5 like “e%”))
group by o.hn

select p.pttype,t.hipdata_code,o.hba1c,o.hn,o.vstdate,o.vn,s.pdx,s.dx0,s.dx1,s.dx2,s.dx3,s.dx4,s.dx5
from opdscreen o
join patient p on p.hn=o.hn
join pttype t on t.pttype=p.pttype
join vn_stat s on s.vn=o.vn
where (o.vstdate between ‘2013-10-01’ and ‘2014-06-31′)
and o.hba1c<>””
and (t.hipdata_code=’UCS’)
and ((s.pdx like “e%”) or (s.dx0 like “e%”) or (s.dx2 like “e%”) or (s.dx3 like “e%”) or (s.dx4 like “e%”) or (s.dx5 like “e%”))
group by o.hn

select p.pttype,t.hipdata_code,o.hba1c,o.hn,o.vstdate,o.vn,s.pdx,s.dx0,s.dx1,s.dx2,s.dx3,s.dx4,s.dx5
from opdscreen o
join patient p on p.hn=o.hn
join pttype t on t.pttype=p.pttype
join vn_stat s on s.vn=o.vn
where (o.vstdate between ‘2013-10-01’ and ‘2014-06-31’)
and o.hba1c<7
and ((s.pdx like “e%”) or (s.dx0 like “e%”) or (s.dx2 like “e%”) or (s.dx3 like “e%”) or (s.dx4 like “e%”) or (s.dx5 like “e%”))
group by o.hn

select p.pttype,t.hipdata_code,o.hba1c,o.hn,o.vstdate,o.vn,s.pdx,s.dx0,s.dx1,s.dx2,s.dx3,s.dx4,s.dx5
from opdscreen o
join patient p on p.hn=o.hn
join pttype t on t.pttype=p.pttype
join vn_stat s on s.vn=o.vn
where (o.vstdate between ‘2013-10-01’ and ‘2014-06-31’)
and o.hba1c<>””
and ((s.pdx like “e%”) or (s.dx0 like “e%”) or (s.dx2 like “e%”) or (s.dx3 like “e%”) or (s.dx4 like “e%”) or (s.dx5 like “e%”))
group by o.hn

select p.pttype,t.hipdata_code,o.hba1c,o.hn,o.vstdate,o.vn,o.bpd,o.bps,s.pdx,s.dx0,s.dx1,s.dx2,s.dx3,s.dx4,s.dx5
from opdscreen o
join patient p on p.hn=o.hn
join pttype t on t.pttype=p.pttype
join vn_stat s on s.vn=o.vn
where (o.vstdate between ‘2013-10-01’ and ‘2014-06-31′)
and (o.bps< 140 or o.bps <90)
and (t.hipdata_code=’UCS’)
and ((s.pdx like “i10”) or (s.dx0 like “i10”) or (s.dx2 like “i10”) or (s.dx3 like “i10”) or (s.dx4 like “i10”) or (s.dx5 like “i10”))
group by o.hn
order by o.vstdate desc

select p.pttype,t.hipdata_code,o.hba1c,o.hn,o.vstdate,o.vn,o.bpd,o.bps,s.pdx,s.dx0,s.dx1,s.dx2,s.dx3,s.dx4,s.dx5
from opdscreen o
join patient p on p.hn=o.hn
join pttype t on t.pttype=p.pttype
join vn_stat s on s.vn=o.vn
where (o.vstdate between ‘2013-10-01’ and ‘2014-06-31′)
and (o.bps< 140 or o.bps <90)
/* and (t.hipdata_code=’UCS’) */
and ((s.pdx like “i10”) or (s.dx0 like “i10”) or (s.dx2 like “i10”) or (s.dx3 like “i10”) or (s.dx4 like “i10”) or (s.dx5 like “i10”))
group by o.hn
order by o.vstdate desc

select pt.*,ov.*
from an_stat ov ,patient pt ,ipt ovst
where ov.an=ovst.an and ov.regdate between “2013-10-01” and “2014-06-25” and ov.hn=pt.hn
and ov.age_y>= 0
and ov.age_y<= 200
and ov.pcode in (
“A1″
,”AA”
,”AB”
,”AC”
,”AD”
,”AE”
,”AF”
,”AG”
,”AI”
,”AJ”
,”AK”
,”UB”
,”UC”
)
and ( (ov.pdx like “e%”)
)

select concat(p.pname,p.fname,’ ‘,p.lname)as name,p.age_y,concat(pt.addrpart,’ ËÁÙè ‘,pt.moopart,’ ‘,t.full_name)as address,
c.hn,v.vstdate,
(case when lo1.lab_order_result< ‘100’ then 1 else 0 end)as 100down,
(case when lo2.lab_order_result between ‘100’ and ‘125’ then 1 else 0 end)as 100to125,
(case when lo3.lab_order_result between ‘126’ and ‘154’ then 1 else 0 end)as 126to154,
(case when lo4.lab_order_result between ‘155’ and ‘182’ then 1 else 0 end)as 155to182,
(case when lo5.lab_order_result > ‘182’ then 1 else 0 end )as 182up,
lo.lab_items_code,lo.lab_order_result
from clinicmember c
left join vn_stat v on v.hn=c.hn
left join lab_head lh on lh.vn=v.vn
left join person p on p.patient_hn=c.hn
left join patient pt on pt.hn=c.hn
left join thaiaddress t on t.chwpart=pt.chwpart and t.amppart=pt.amppart and t.tmbpart=pt.tmbpart
left join lab_order lo on lo.lab_order_number=lh.lab_order_number
left join lab_order lo1 on lo1.lab_order_number=lh.lab_order_number and lo1.lab_items_code=’377′ and lo1.lab_order_result <100
left join lab_order lo2 on lo2.lab_order_number=lh.lab_order_number and lo2.lab_items_code=’377′ and (lo2.lab_order_result between ‘100’ and ‘125’)
left join lab_order lo3 on lo3.lab_order_number=lh.lab_order_number and lo3.lab_items_code=’377′ and (lo3.lab_order_result between ‘126’ and ‘154’)
left join lab_order lo4 on lo4.lab_order_number=lh.lab_order_number and lo4.lab_items_code=’377′ and (lo4.lab_order_result between ‘155’ and ‘182’)
left join lab_order lo5 on lo5.lab_order_number=lh.lab_order_number and lo5.lab_items_code=’377′ and lo5.lab_order_result > 182
where (v.vstdate between ‘2014-04-01’ and ‘2014-04-04’) and (c.clinic in (‘001′) and lo.lab_items_code =’377’) and ((v.pdx between ‘e110’ and ‘e119’)
or (v.dx0 between ‘e110’ and ‘e119’) or (v.dx1 between ‘e110’ and ‘e119’) or (v.dx2 between ‘e110’ and ‘e119’) or (v.dx3 between ‘e110’ and ‘e119’)
or (v.dx4 between ‘e110’ and ‘e119’) or (v.dx5 between ‘e110’ and ‘e119′) )
order by lo5.lab_order_result,lo4.lab_order_result,lo3.lab_order_result,lo2.lab_order_result,lo1.lab_order_result

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

select concat(p.pname,p.fname,’ ‘,p.lname)as name,p.age_y,concat(pt.addrpart,’ ËÁÙè ‘,pt.moopart,’ ‘,t.full_name)as address,
c.hn,v.vstdate,(lo1.lab_order_result)as 100down,(lo2.lab_order_result)as 100to125,(lo3.lab_order_result)as 126to154,
(lo4.lab_order_result) as 155to182,(lo5.lab_order_result)as 182up,lo.lab_items_code,lo.lab_order_result
from clinicmember c
left join vn_stat v on v.hn=c.hn
left join lab_head lh on lh.vn=v.vn
left join person p on p.patient_hn=c.hn
left join patient pt on pt.hn=c.hn
left join thaiaddress t on t.chwpart=pt.chwpart and t.amppart=pt.amppart and t.tmbpart=pt.tmbpart
left join lab_order lo on lo.lab_order_number=lh.lab_order_number
left join lab_order lo1 on lo1.lab_order_number=lh.lab_order_number and lo1.lab_items_code=’377′ and lo1.lab_order_result <100
left join lab_order lo2 on lo2.lab_order_number=lh.lab_order_number and lo2.lab_items_code=’377’ and (lo2.lab_order_result between ‘100’ and ‘125’)
left join lab_order lo3 on lo3.lab_order_number=lh.lab_order_number and lo3.lab_items_code=’377′ and (lo3.lab_order_result between ‘126’ and ‘154’)
left join lab_order lo4 on lo4.lab_order_number=lh.lab_order_number and lo4.lab_items_code=’377′ and (lo4.lab_order_result between ‘155’ and ‘182’)
left join lab_order lo5 on lo5.lab_order_number=lh.lab_order_number and lo5.lab_items_code=’377′ and lo5.lab_order_result > 182
where (v.vstdate between ‘2014-01-01’ and ‘2014-01-01’) and (c.clinic in (‘001′) and lo.lab_items_code =’377’) and ((v.pdx between ‘e110’ and ‘e119’)
or (v.dx0 between ‘e110’ and ‘e119’) or (v.dx1 between ‘e110’ and ‘e119’) or (v.dx2 between ‘e110’ and ‘e119’) or (v.dx3 between ‘e110’ and ‘e119’)
or (v.dx4 between ‘e110’ and ‘e119’) or (v.dx5 between ‘e110’ and ‘e119’) )
order by lo5.lab_order_result,lo4.lab_order_result,lo3.lab_order_result,lo2.lab_order_result,lo1.lab_order_result

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

select concat(p.pname,p.fname,’ ‘,p.lname)as name,t.full_name,c.hn,c.screen_date,c.vn,concat(p.chwpart,p.amppart,p.tmbpart)as tbb,c.*
from clinicmember_cormobidity_screen c
left join clinicmember_cormobidity_eye_screen c2 on c2.clinicmember_cormobidity_screen_id=c.clinicmember_cormobidity_screen_id
left join patient p on p.hn=c.hn
left join thaiaddress t on t.chwpart=p.chwpart and t.amppart=p.amppart and t.tmbpart=p.tmbpart
where (c.screen_date between ‘2013-10-01’ and ‘2014-05-31′) and (c.do_eye_screen=’Y’)
group by c.hn
order by c.screen_date,full_name

select concat(p.pname,p.fname,’ ‘,p.lname)as name,t.full_name,c.hn,c.screen_date,c.vn,concat(p.chwpart,p.amppart,p.tmbpart)as tbb,c2.*
from clinicmember_cormobidity_screen c
left join clinicmember_cormobidity_foot_screen c2 on c2.clinicmember_cormobidity_screen_id=c.clinicmember_cormobidity_screen_id
left join patient p on p.hn=c.hn
left join thaiaddress t on t.chwpart=p.chwpart and t.amppart=p.amppart and t.tmbpart=p.tmbpart
where (c.screen_date between ‘2013-10-01’ and ‘2014-05-31′) and (p.chwpart=’32’ and p.amppart=’04’ and p.tmbpart=’01’)
group by c.hn
order by c.screen_date

select c.*
from clinicmember_cormobidity_screen c
join patient p on p.hn=c.hn
join pttype t on t.pttype=p.pttype
join vn_stat s on s.vn=c.vn

where (c.screen_date between ‘2013-10-01’ and ‘2014-06-30′)
and (c.do_foot_screen =’Y’)
and (t.hipdata_code=’UCS’)
group by c.hn

select c.*
from clinicmember_cormobidity_screen c
join patient p on p.hn=c.hn
join pttype t on t.pttype=p.pttype
join vn_stat s on s.vn=c.vn

where (c.screen_date between ‘2013-10-01’ and ‘2014-06-30′)
and (c.do_eye_screen =’Y’)
and (t.hipdata_code=’UCS’)
group by c.hn