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

select v.vstdate,v.hn,v.cid,p2.pttype_no,pt.name,concat(p.pname,p.fname,’ ‘,p.lname),
p.sex,v.age_y,v.pdx,v.dx0,v.dx1,v.dx2,v.dx3,v.dx4,v.dx5,
v.hospmain,v.hospsub,v.pcode,v.pttype /* ,v.inc01,v.inc02,v.inc03,v.inc04,v.inc05,v.inc06,
v.inc07,v.inc08,v.inc09,v.inc10,v.inc11,v.inc12,v.inc13,v.inc14,v.inc15,v.inc16,v.income */
from vn_stat v
left outer join patient p on p.hn=v.hn
left join person p2 on p2.cid=v.cid
left join pttype pt on pt.pcode=v.pcode
where v.vstdate between ‘2014-07-30’ and ‘2014-07-31’

select * from person    where (house_regist_type_id in (‘1′,’3′)) and (age_y between ’20’ and ’50’)

select * from ovstdiag where icd10 between ‘f20’ and ‘f299’ and vstdate between ‘2014-06-01’ and ‘2014-06-31’
group by hn

type 4 ที่อยู่ในเขต

select concat(o.staff,’ ‘,min(o.vstdate))as st_vst,p.*
from patient p
left outer join ovst o on o.hn=p.hn

where p.type_area in (‘4′) and p.chwpart=’32’ and p.amppart=’04’ and p.tmbpart=’01’
group by o.hn

type 1  ที่อยู่นอกเขต

select concat(o.staff,’ ‘,min(o.vstdate))as st_vst,p.*

from patient p
left outer join ovst o on o.hn=p.hn

where p.type_area in (‘1′) and concat(p.chwpart,p.amppart,p.tmbpart)<>’320401’
group by o.hn

select p.hipdata_code,h.hn,h.vn,h.order_date,o.pdx,o.dx0,o.dx1,o.dx2,o.dx3,o.dx4,o.dx5,d.* from lab_order d left join lab_head h on h.lab_order_number=d.lab_order_number left join vn_stat o on o.vn=h.vn left join pttype p on p.pttype=o.pttype where (p.hipdata_code=’UCS’) and (h.order_date between ‘2013-10-01’ and ‘2014-05-31′) and d.lab_items_code=’225’ and ((o.pdx between ‘e10’ and ‘e14’ ) or (o.dx0 between ‘e10’ and ‘e14’ ) or (o.dx1 between ‘e10’ and ‘e14’ )or (o.dx2 between ‘e10’ and ‘e14’ ) or (o.dx3 between ‘e10’ and ‘e14’ ) or (o.dx4 between ‘e10’ and ‘e14’ ) or (o.dx5 between ‘e10’ and ‘e14’ )) and (d.lab_order_result like ‘N%’) group by h.hn

หาค่า  bps และ bpd พี่เหมียว

select o.*
from opdscreen o
left join vn_stat v on v.vn=o.vn
left join pttype p on p.pttype=v.pttype
where (p.hipdata_code=’UCS’)and (o.vstdate between ‘2013-10-01’ and ‘2014-05-31’) and ((v.pdx between ‘e10’ and ‘e14’) or (v.dx0 between ‘e10’ and ‘e14’) or
(v.dx1 between ‘e10’ and ‘e14’) or(v.dx2 between ‘e10’ and ‘e14’) or(v.dx3 between ‘e10’ and ‘e14’) or(v.dx4 between ‘e10’ and ‘e14’) or
(v.dx5 between ‘e10’ and ‘e14′)) and (o.bps<=130 and o.bpd<=80)
group by hn

ตาม URINE ANALYSIS  ทั้งหมด

select p.hipdata_code,h.hn,h.vn,h.order_date,o.pdx,o.dx0,o.dx1,o.dx2,o.dx3,o.dx4,o.dx5,d.*
from lab_order d
left join lab_head h on h.lab_order_number=d.lab_order_number
left join vn_stat o on o.vn=h.vn
left join pttype p on p.pttype=o.pttype
left join lab_items i on i.lab_items_code=d.lab_items_code
where ( p.hipdata_code=’ucs’) and (h.order_date between ‘2013-10-01’ and ‘2014-05-31’) and (i.lab_items_group in (‘2′) )
and ((o.pdx=’i10′ or o.pdx=’i152′ ) or (o.dx0=’i10′ or o.dx0=’i152′) or (o.dx1=’i10′ or o.dx1=’i152′ )
or (o.dx2=’i10′ or o.dx2=’i152′ ) or (o.dx3=’i10′ or o.dx3=’i152′ ) or (o.dx4=’i10′ or o.dx4=’i152′ )
or (o.dx5=’i10′ or o.dx5=’i152′ ))
group by h.hn

หาค่า fbs

select o.*
from opdscreen o
left join vn_stat v on v.vn=o.vn
left join pttype p on p.pttype=v.pttype
where (p.hipdata_code=’UCS’) and (o.vstdate between ‘2013-10-01’ and ‘2014-05-31′)
and ((v.pdx=’i10′ or v.pdx=’i152′ ) or (v.dx0=’i10′ or v.dx0=’i152′)
or (v.dx1=’i10′ or v.dx1=’i152′ ) or (v.dx2=’i10′ or v.dx2=’i152′ )
or (v.dx3=’i10′ or v.dx3=’i152′ ) or (v.dx4=’i10′ or v.dx4=’i152′ )
or (v.dx5=’i10′ or v.dx5=’i152’ )) and (o.fbs<>”)
group by hn

หา ความดันแทรกซ้อนไต

select v.pdx,v.dx0,v.dx1,v.dx2,v.dx3,v.dx4,v.dx5,o.*
from opdscreen o
left join vn_stat v on v.vn=o.vn
left join pttype p on p.pttype=v.pttype
where (o.vstdate between ‘2013-10-01’ and ‘2014-05-31′)
and (((v.pdx=’i10′ or v.pdx=’i152′) and v.dx0=’N189′ ) or ((v.pdx=’i10′ or v.pdx=’i152’) and v.dx1= ‘N189′ )
or ((v.pdx=’i10′ or v.pdx=’i152′) and v.dx2=’N189′ ) or ((v.pdx=’i10′ or v.pdx=’i152’) and v.dx3 = ‘N189′ )
or ((v.pdx=’i10′ or v.pdx=’i152′) and v.dx4 =’N189′ ) or ((v.pdx=’i10′ or v.pdx=’i152’) and v.dx5 = ‘N189′ )
or
((v.dx0=’i10′ or v.dx0=’i152′) and v.pdx=’N189′ ) or ((v.dx0=’i10′ or v.dx0=’i152’) and v.dx1= ‘N189′ )
or ((v.dx0=’i10′ or v.dx0=’i152′) and v.dx2=’N189′ ) or ((v.dx0=’i10′ or v.dx0=’i152’) and v.dx3 = ‘N189′ )
or ((v.dx0=’i10′ or v.dx0=’i152′) and v.dx4 =’N189′ ) or ((v.dx0=’i10′ or v.dx0=’i152’) and v.dx5 = ‘N189′ )
or
((v.dx1=’i10′ or v.dx1=’i152′) and v.pdx=’N189′ ) or ((v.dx1=’i10′ or v.dx1=’i152’) and v.dx0= ‘N189′ )
or ((v.dx1=’i10′ or v.dx1=’i152′) and v.dx2=’N189′ ) or ((v.dx1=’i10′ or v.dx1=’i152’) and v.dx3 = ‘N189′ )
or ((v.dx1=’i10′ or v.dx1=’i152′) and v.dx4 =’N189′ ) or ((v.dx1=’i10′ or v.dx1=’i152’) and v.dx5 = ‘N189′ )
or
((v.dx2=’i10′ or v.dx2=’i152′) and v.pdx=’N189′ ) or ((v.dx2=’i10′ or v.dx2=’i152’) and v.dx1= ‘N189′ )
or ((v.dx2=’i10′ or v.dx2=’i152′) and v.dx0=’N189′ ) or ((v.dx2=’i10′ or v.dx2=’i152’) and v.dx3 = ‘N189′ )
or ((v.dx2=’i10′ or v.dx2=’i152′) and v.dx4 =’N189′ ) or ((v.dx2=’i10′ or v.dx2=’i152’) and v.dx5 = ‘N189′ )
or
((v.dx3=’i10′ or v.dx3=’i152′) and v.pdx=’N189′ ) or ((v.dx3=’i10′ or v.dx3=’i152’) and v.dx1= ‘N189′ )
or ((v.dx3=’i10′ or v.dx3=’i152′) and v.dx2=’N189′ ) or ((v.dx3=’i10′ or v.dx3=’i152’) and v.dx0 = ‘N189′ )
or ((v.dx3=’i10′ or v.dx3=’i152′) and v.dx4 =’N189′ ) or ((v.dx3=’i10′ or v.dx3=’i152’) and v.dx5 = ‘N189′ )
or
((v.dx4=’i10′ or v.dx4=’i152′) and v.pdx=’N189′ ) or ((v.dx4=’i10′ or v.dx4=’i152’) and v.dx1= ‘N189′ )
or ((v.dx4=’i10′ or v.dx4=’i152′) and v.dx2=’N189′ ) or ((v.dx4=’i10′ or v.dx4=’i152’) and v.dx3 = ‘N189′ )
or ((v.dx4=’i10′ or v.dx4=’i152′) and v.dx0 =’N189′ ) or ((v.dx4=’i10′ or v.dx4=’i152’) and v.dx5 = ‘N189′ )
or
((v.dx5=’i10′ or v.dx5=’i152′) and v.pdx=’N189′ ) or ((v.dx5=’i10′ or v.dx5=’i152’) and v.dx1= ‘N189′ )
or ((v.dx5=’i10′ or v.dx5=’i152′) and v.dx2=’N189′ ) or ((v.dx5=’i10′ or v.dx5=’i152’) and v.dx3 = ‘N189′ )
or ((v.dx5=’i10′ or v.dx5=’i152′) and v.dx4 =’N189′ ) or ((v.dx5=’i10′ or v.dx5=’i152’) and v.dx0 = ‘N189’ )

)
group by hn

หา ความดันแทรกทางสมอง

select v.pdx,v.dx0,v.dx1,v.dx2,v.dx3,v.dx4,v.dx5,o.*
from opdscreen o
left join vn_stat v on v.vn=o.vn
left join pttype p on p.pttype=v.pttype
where (o.vstdate between ‘2013-10-01’ and ‘2014-05-31′)
and (((v.pdx=’i10′ or v.pdx=’i152′) and v.dx0=’I64′ ) or ((v.pdx=’i10′ or v.pdx=’i152’) and v.dx1= ‘I64′ )
or ((v.pdx=’i10′ or v.pdx=’i152′) and v.dx2=’I64′ ) or ((v.pdx=’i10′ or v.pdx=’i152’) and v.dx3 = ‘I64′ )
or ((v.pdx=’i10′ or v.pdx=’i152′) and v.dx4 =’I64′ ) or ((v.pdx=’i10′ or v.pdx=’i152’) and v.dx5 = ‘I64′ )
or
((v.dx0=’i10′ or v.dx0=’i152′) and v.pdx=’I64′ ) or ((v.dx0=’i10′ or v.dx0=’i152’) and v.dx1= ‘I64′ )
or ((v.dx0=’i10′ or v.dx0=’i152′) and v.dx2=’I64′ ) or ((v.dx0=’i10′ or v.dx0=’i152’) and v.dx3 = ‘I64′ )
or ((v.dx0=’i10′ or v.dx0=’i152′) and v.dx4 =’I64′ ) or ((v.dx0=’i10′ or v.dx0=’i152’) and v.dx5 = ‘I64′ )
or
((v.dx1=’i10′ or v.dx1=’i152′) and v.pdx=’I64′ ) or ((v.dx1=’i10′ or v.dx1=’i152’) and v.dx0= ‘I64′ )
or ((v.dx1=’i10′ or v.dx1=’i152′) and v.dx2=’I64′ ) or ((v.dx1=’i10′ or v.dx1=’i152’) and v.dx3 = ‘I64′ )
or ((v.dx1=’i10′ or v.dx1=’i152′) and v.dx4 =’I64′ ) or ((v.dx1=’i10′ or v.dx1=’i152’) and v.dx5 = ‘I64′ )
or
((v.dx2=’i10′ or v.dx2=’i152′) and v.pdx=’I64′ ) or ((v.dx2=’i10′ or v.dx2=’i152’) and v.dx1= ‘I64′ )
or ((v.dx2=’i10′ or v.dx2=’i152′) and v.dx0=’I64′ ) or ((v.dx2=’i10′ or v.dx2=’i152’) and v.dx3 = ‘I64′ )
or ((v.dx2=’i10′ or v.dx2=’i152′) and v.dx4 =’I64′ ) or ((v.dx2=’i10′ or v.dx2=’i152’) and v.dx5 = ‘I64′ )
or
((v.dx3=’i10′ or v.dx3=’i152′) and v.pdx=’I64′ ) or ((v.dx3=’i10′ or v.dx3=’i152’) and v.dx1= ‘I64′ )
or ((v.dx3=’i10′ or v.dx3=’i152′) and v.dx2=’I64′ ) or ((v.dx3=’i10′ or v.dx3=’i152’) and v.dx0 = ‘I64′ )
or ((v.dx3=’i10′ or v.dx3=’i152′) and v.dx4 =’I64′ ) or ((v.dx3=’i10′ or v.dx3=’i152’) and v.dx5 = ‘I64′ )
or
((v.dx4=’i10′ or v.dx4=’i152′) and v.pdx=’I64′ ) or ((v.dx4=’i10′ or v.dx4=’i152’) and v.dx1= ‘I64′ )
or ((v.dx4=’i10′ or v.dx4=’i152′) and v.dx2=’I64′ ) or ((v.dx4=’i10′ or v.dx4=’i152’) and v.dx3 = ‘I64′ )
or ((v.dx4=’i10′ or v.dx4=’i152′) and v.dx0 =’I64′ ) or ((v.dx4=’i10′ or v.dx4=’i152’) and v.dx5 = ‘I64′ )
or
((v.dx5=’i10′ or v.dx5=’i152′) and v.pdx=’I64′ ) or ((v.dx5=’i10′ or v.dx5=’i152’) and v.dx1= ‘I64′ )
or ((v.dx5=’i10′ or v.dx5=’i152′) and v.dx2=’I64′ ) or ((v.dx5=’i10′ or v.dx5=’i152’) and v.dx3 = ‘I64′ )
or ((v.dx5=’i10′ or v.dx5=’i152′) and v.dx4 =’I64′ ) or ((v.dx5=’i10′ or v.dx5=’i152’) and v.dx0 = ‘I64’ )

)
group by hn

หาความดันแทรกซ้อนทางหัวใจ

select v.pdx,v.dx0,v.dx1,v.dx2,v.dx3,v.dx4,v.dx5,o.*
from opdscreen o
left join vn_stat v on v.vn=o.vn
left join pttype p on p.pttype=v.pttype
where (o.vstdate between ‘2013-10-01’ and ‘2014-05-31′)
and (((v.pdx=’i10′ or v.pdx=’i152′) and v.dx0=’I259′ ) or ((v.pdx=’i10′ or v.pdx=’i152’) and v.dx1= ‘I259′ )
or ((v.pdx=’i10′ or v.pdx=’i152′) and v.dx2=’I259′ ) or ((v.pdx=’i10′ or v.pdx=’i152’) and v.dx3 = ‘I259′ )
or ((v.pdx=’i10′ or v.pdx=’i152′) and v.dx4 =’I259′ ) or ((v.pdx=’i10′ or v.pdx=’i152’) and v.dx5 = ‘I259′ )
or
((v.dx0=’i10′ or v.dx0=’i152′) and v.pdx=’I259′ ) or ((v.dx0=’i10′ or v.dx0=’i152’) and v.dx1= ‘I259′ )
or ((v.dx0=’i10′ or v.dx0=’i152′) and v.dx2=’I259′ ) or ((v.dx0=’i10′ or v.dx0=’i152’) and v.dx3 = ‘I259′ )
or ((v.dx0=’i10′ or v.dx0=’i152′) and v.dx4 =’I259′ ) or ((v.dx0=’i10′ or v.dx0=’i152’) and v.dx5 = ‘I259′ )
or
((v.dx1=’i10′ or v.dx1=’i152′) and v.pdx=’I259′ ) or ((v.dx1=’i10′ or v.dx1=’i152’) and v.dx0= ‘I259′ )
or ((v.dx1=’i10′ or v.dx1=’i152′) and v.dx2=’I259′ ) or ((v.dx1=’i10′ or v.dx1=’i152’) and v.dx3 = ‘I259′ )
or ((v.dx1=’i10′ or v.dx1=’i152′) and v.dx4 =’I259′ ) or ((v.dx1=’i10′ or v.dx1=’i152’) and v.dx5 = ‘I259′ )
or
((v.dx2=’i10′ or v.dx2=’i152′) and v.pdx=’I259′ ) or ((v.dx2=’i10′ or v.dx2=’i152’) and v.dx1= ‘I259′ )
or ((v.dx2=’i10′ or v.dx2=’i152′) and v.dx0=’I259′ ) or ((v.dx2=’i10′ or v.dx2=’i152’) and v.dx3 = ‘I259′ )
or ((v.dx2=’i10′ or v.dx2=’i152′) and v.dx4 =’I259′ ) or ((v.dx2=’i10′ or v.dx2=’i152’) and v.dx5 = ‘I259′ )
or
((v.dx3=’i10′ or v.dx3=’i152′) and v.pdx=’I259′ ) or ((v.dx3=’i10′ or v.dx3=’i152’) and v.dx1= ‘I259′ )
or ((v.dx3=’i10′ or v.dx3=’i152′) and v.dx2=’I259′ ) or ((v.dx3=’i10′ or v.dx3=’i152’) and v.dx0 = ‘I259′ )
or ((v.dx3=’i10′ or v.dx3=’i152′) and v.dx4 =’I259′ ) or ((v.dx3=’i10′ or v.dx3=’i152’) and v.dx5 = ‘I259′ )
or
((v.dx4=’i10′ or v.dx4=’i152′) and v.pdx=’I259′ ) or ((v.dx4=’i10′ or v.dx4=’i152’) and v.dx1= ‘I259′ )
or ((v.dx4=’i10′ or v.dx4=’i152′) and v.dx2=’I259′ ) or ((v.dx4=’i10′ or v.dx4=’i152’) and v.dx3 = ‘I259′ )
or ((v.dx4=’i10′ or v.dx4=’i152′) and v.dx0 =’I259′ ) or ((v.dx4=’i10′ or v.dx4=’i152’) and v.dx5 = ‘I259′ )
or
((v.dx5=’i10′ or v.dx5=’i152′) and v.pdx=’I259′ ) or ((v.dx5=’i10′ or v.dx5=’i152’) and v.dx1= ‘I259′ )
or ((v.dx5=’i10′ or v.dx5=’i152′) and v.dx2=’I259′ ) or ((v.dx5=’i10′ or v.dx5=’i152’) and v.dx3 = ‘I259′ )
or ((v.dx5=’i10′ or v.dx5=’i152′) and v.dx4 =’I259′ ) or ((v.dx5=’i10′ or v.dx5=’i152’) and v.dx0 = ‘I259’ )

)
group by hn

หา เบาหวาน แทรกซ้อนไต

select * from ovstdiag where (vstdate between ‘2013-10-01’ and ‘2014-05-31’) and icd10 like ‘%e112%’
group by hn

select
sum(case when fbs_before between ‘140’ and ‘199’ then 1 else 0 end)as fbsB,
sum(case when bps_before between ‘120’ and ‘139’ then 1 else 0 end)as bpsB,
sum(case when bpd_before between ’80’ and ’80’ then 1 else 0 end)as bpdB,
sum(case when p.sex=’1′ and waist_before> ’90’ then 1 else 0 end)as waistM,
sum(case when p.sex=’2′ and waist_before> ’80’ then 1 else 0 end)as waistW,
sum(case when fbs_after<140 then 1 else 0 end)as fbs_down_140,
sum(case when fbs_after>=140 then 1 else 0 end)as fbs_upand_140,
sum(case when bps_after<120 then 1 else 0 end)as bps_down_120,
sum(case when bps_after>=120 then 1 else 0 end)as bps_upand_120,
sum(case when bpd_after<80 then 1 else 0 end)as bpd_down_80,
sum(case when bpd_after>=80 then 1 else 0 end)as bpd_upand_80,
sum(case when p.sex=’1′ and waist_after<90 then 1 else 0 end)as waist_afterM_down90,
sum(case when p.sex=’1′ and waist_after>90 then 1 else 0 end)as waist_afterM_up90,
sum(case when p.sex=’2′ and waist_after<80 then 1 else 0 end)as waist_afterW_down80,
sum(case when p.sex=’2′ and waist_after>80 then 1 else 0 end)as waist_afterW_up90,

concat(p.pname,p.fname,’ ‘,p.lname)as name, p.patient_hn,p.person_id,rs.*
from person_dmht_risk_service rs
left join person_dmht_risk_register rr on rr.person_dmht_risk_register_id=rs.person_dmht_risk_register_id
left join person p on p.person_id=rr.person_id
where rs.service_before_check_date between ‘2014-01-01’ and ‘2014-08-30’

select v.village_moo,v.village_name,p.pname,p.fname,p.lname,p.house_regist_type_id,p.village_id,p.cid,
p2.hn,p2.type_area,p2.addrpart,p2.moopart,p2.tmbpart,p2.amppart,p2.chwpart,d.person_discharge_name,p.last_update
from person p
join village v on v.village_id=p.village_id
join patient p2 on p2.cid=p.cid
join person_discharge d on d.person_discharge_id=p.person_discharge_id
where p.house_regist_type_id in (‘4’) and p.village_id <> 1