รายงานตรวจสุขภาพประจำปี

[bit48010010]

ขอเพิ่มที่อยู่ให้หน่อยครับ ที่เหลือโอเคหมดละ ผมทำมาทั้งวันละ งง หมดแล้ว มือใหม่หัดทำรายงานครับ

รายงานความดันเบาหวาน ส่งสปสช. ข้อมูลเบื้อง ต้น LAB  BP
select cli.clinic,cli.name as '¤ÅÕ¹Ô¡',vns.vstdate as 'ÇѹÁÒ',vns.hn,pt.cid,concat(pt.pname,"",pt.fname,"  ",pt.lname) as 'ª×èÍ Ê¡ØÅ',
pt.sex,vns.age_y,pt.informaddr as '·ÕèÍÂÙè',vns.pdx,opd.bps,opd.bpd,opd.fbs,
pt.in_cups as 'ã¹ cup',labh.order_date as 'Çѹ·ÕèµÃǨ',
labi.lab_items_name,labi.sub_group_list,labor.lab_order_result
from vn_stat vns
left outer join patient pt on pt.hn=vns.hn
left outer join opdscreen opd on opd.vn=vns.vn
left outer join lab_head labh on labh.vn=vns.vn
left outer join clinicmember clim on clim.hn=vns.hn
left outer join clinic cli on cli.clinic=clim.clinic
left outer join lab_order labor on labor.lab_order_number=labh.lab_order_number
left outer join lab_items labi on labi.lab_items_code=labor.lab_items_code
where vns.vstdate between "2009-10-01" and "2010-03-31"
and cli.clinic in ('001','002','009')
group by vns.hn
order by vns.vstdate




ระดับ FBS หรือ DTX ในผู้ป่วยคลินิก เบาหวาน <=130  ผู้ป่วยทุกคน
select v.hn,v.vstdate,c.number,concat(p.pname,p.fname,"  ",p.lname) as ptname,lb.lab_order_result as LDL
 from vn_stat  v
left outer join lab_head lh on lh.vn=v.vn
left outer join opdscreen o on o.vn=v.vn
left outer join patient p on p.hn=v.hn
left outer join lab_order lb on lb.lab_order_number=lh.lab_order_number
left outer join clinicmember c on c.hn=v.hn
where  v.vstdate between '2009-02-01' and '2009-06-31' and c.clinic ="001" and v.spclty ="01" and  lb.lab_order_result <=130
and lb.lab_items_code in ('3073', '3045', '3001', '3036')

ถ้าจะแสดงเฉพาะ ผู้ที่อยู่ใน คลินิก dm ต้องเพิ่ม เงื่อนไข where ดังนี้ครับ

and v.hn in (select hn from clinicmember where clinic='001')












รายงานLab HbA1C <7
select c.hn,o.vstdate,li.lab_items_name,lo.lab_order_result
from clinicmember c
     left outer join ovst o on o.hn=c.hn
     left outer join lab_head lh on lh.vn=o.vn
     left outer join lab_order lo on lo.lab_order_number=lh.lab_order_number
     left outer join lab_items li on li.lab_items_code=lo.lab_items_code
     where c.clinic ="001" and c.new_case="Y" and li.lab_items_name="HbA1C" and o.vstdate between '2009-10-01' and '2010-06-30' and lo.lab_order_result < 7

รายงานผู้ป่วยเบาหวาน ต.หางดงที่คุมน้ำตาล 3 เดือนไม่ได้มากกว่า 126 mg%
select c.* ,p.*
from clinicmember c
left outer join patient p on p.hn=c.hn
where c.clinic = (select sys_value from sys_var where sys_name="dm_clinic_code")
and c.hn in (select hn from fbshistory where ((fbs1mo+fbs2mo+fbs3mo)/3  > 126)  or ((fbs1mo+fbs2mo)/2  > 126) )
and c.hn in (select hn from patient where chwpart="50" and amppart="16" and tmbpart="01" and moopart in ("1","2","3","4","5","6","7","8","9","10","11","12","13")and birthday < "1946-08-01"
  )

รายงานผู้ป่วยเบาหวาน ผลLAB
select v.hn,v.vstdate,c.number,concat(p.pname,p.fname,"  ",p.lname) as ptname,o.bw,o.height,o.bpd,o.bps,
o.bmi,o.tg,o.hdl,o.bun,o.creatinine,o.ua,lb.lab_order_result as fbs
 from vn_stat  v
left outer join lab_head lh on lh.vn=v.vn
left outer join opdscreen o on o.vn=v.vn
left outer join patient p on p.hn=v.hn
left outer join lab_order lb on lb.lab_order_number=lh.lab_order_number
left outer join clinicmember c on c.hn=v.hn
where  v.vstdate between '2010-02-01' and '2010-03-31'and c.clinic ="001" and v.spclty ="28" and lb.lab_items_code ="76""77""78""79""91""92""193""405"


รายงานผู้ป่วยเบาหวาน ผลLAB
select v.hn,v.vstdate,c.number,concat(p.pname,p.fname,"  ",p.lname) as ptname,o.bw,o.height,o.bpd,o.bps,
o.bmi,o.tg,o.ldl, o.hdl, o.hbA1c,o.creatinine,o.ua,lb.lab_order_result as fbs
 from vn_stat  v
left outer join lab_head lh on lh.vn=v.vn
left outer join opdscreen o on o.vn=v.vn
left outer join patient p on p.hn=v.hn
left outer join lab_order lb on lb.lab_order_number=lh.lab_order_number
left outer join clinicmember c on c.hn=v.hn
where  v.vstdate between '2010-01-01' and '2010-06-30'and c.clinic ="001" and v.spclty ="28" and lb.lab_items_code ="76""77""78""79""91""92""193""405"










รายงานผู้ป่วยความดัน ผลLAB
select v.hn,v.vstdate,c.number,concat(p.pname,p.fname,"  ",p.lname) as ptname,o.bw,o.height,o.bpd,o.bps,
o.bmi,o.tg,o.hdl,o.bun,o.creatinine,o.ua,lb.lab_order_result as fbs
 from vn_stat  v
left outer join lab_head lh on lh.vn=v.vn
left outer join opdscreen o on o.vn=v.vn
left outer join patient p on p.hn=v.hn
left outer join lab_order lb on lb.lab_order_number=lh.lab_order_number
left outer join clinicmember c on c.hn=v.hn
where  v.vstdate between '2010-01-01' and '2010-06-30'and c.clinic ="002" and v.spclty ="30" and lb.lab_items_code ="76""77""78""79""91""92""193""405"








ผู้ป่วยความมีlab LDL
select v.hn,v.vstdate,c.number,concat(p.pname,p.fname,"  ",p.lname) as ptname,o.bw,o.height,o.bpd,o.bps,
o.bmi,o.tg,o.ldl,o.bun,o.creatinine,o.ua,lb.lab_order_result as fbs
 from vn_stat  v
left outer join lab_head lh on lh.vn=v.vn
left outer join opdscreen o on o.vn=v.vn
left outer join patient p on p.hn=v.hn
left outer join lab_order lb on lb.lab_order_number=lh.lab_order_number
left outer join clinicmember c on c.hn=v.hn
where  v.vstdate between '2010-01-01' and '2010-06-30'and c.clinic ="002" and v.spclty ="30" and lb.lab_items_code ="76""77""78""79""91""92""193""405"





คำสั่งดูข้อมูลระดับน้ำตาลตามค่าที่ต้องการ ในผู้ป่วยคลินิก DM ดังนี้ HNซ้ำกัน
select v.hn,v.vstdate,c.number,concat(p.pname,p.fname,"  ",p.lname) as ptname,lb.lab_order_result as LDL
 from vn_stat  v
left outer join lab_head lh on lh.vn=v.vn
left outer join opdscreen o on o.vn=v.vn
left outer join patient p on p.hn=v.hn
left outer join lab_order lb on lb.lab_order_number=lh.lab_order_number
left outer join clinicmember c on c.hn=v.hn
where  v.vstdate between '2008-10-01' and '2009-09-30' and c.clinic ="001" and v.spclty ="01"
and  lb.lab_order_result>=131 and lb.lab_order_result <=179
and lb.lab_items_code in ('3073', '3045', '3001', '3036')


HNไม่ซ้ำ


select distinct(v.hn),v.vstdate,c.number,concat(p.pname,p.fname,"  ",p.lname) as ptname,lb.lab_order_result as LDL
 from vn_stat  v
left outer join lab_head lh on lh.vn=v.vn
left outer join opdscreen o on o.vn=v.vn
left outer join patient p on p.hn=v.hn
left outer join lab_order lb on lb.lab_order_number=lh.lab_order_number
left outer join clinicmember c on c.hn=v.hn
where  v.vstdate between '2008-10-01' and '2009-09-30' and c.clinic ="001" and v.spclty ="01"
and  lb.lab_order_result>=131 and lb.lab_order_result <=179
and lb.lab_items_code in ('76', '77', '78', '92')



select t2.hn from
(select t1.hn, count(t1.vn) as times from
(select o2.vn,o2.hn,o2.vstdate,o2.icd10
from ovstdiag o2
where (o2.vstdate between "2010-01-01" and "2010-01-31") and ((o2.icd10 between "E10" and "E14") or (o2.icd10="I10"))) as t1
group by t1.vn
having count(t1.vn) = 2) as t2
group by t2.hn




select distinct(t1.hn), count(t1.vn) as times from
(select o1.vn,o1.hn,o1.vstdate,o2.icd10
from ovst o1
join ovstdiag o2 on o2.vn=o1.vn
where (o1.vstdate between "2010-01-01" and "2010-01-31") and ((o2.icd10 between "E10" and "E14") or (o2.icd10="I10"))) as t1
group by t1.vn
having count(t1.vn) = 2



รายงานผู้ป่วยทุกสิทธิ  BP  BMI ผลLAB
select v.hn,v.vstdate,v.cid,v.pttype,concat(p.pname,p.fname,"  ",p.lname) as ptname,o.bw,o.height,o.bpd,o.bps,
o.bmi,o.tg,o.hdl, o.ldl,o.bun,o.creatinine,o.cholesterol,lb.lab_order_result as fbs
 from vn_stat  v
left outer join lab_head lh on lh.vn=v.vn
left outer join opdscreen o on o.vn=v.vn
left outer join patient p on p.hn=v.hn
left outer join lab_order lb on lb.lab_order_number=lh.lab_order_number
where  v.vstdate between '2010-02-01' and '2010-03-31'
and lb.lab_items_code ="76""91""92""102""91""92""102""103"




รายงานผู้ป่วยทุกสิทธิ  BP  BMI ผลLAB
select v.hn,v.vstdate,v.cid,v.pttype,concat(p.pname,p.fname,"  ",p.lname) as ptname,o.bw,o.height,o.bpd,o.bps,
o.bmi,o.tg,o.hdl, o.ldl,o.bun,o.creatinine,o.cholesterol,lb.lab_order_result as fbs
 from vn_stat  v
left outer join lab_head lh on lh.vn=v.vn
left outer join opdscreen o on o.vn=v.vn
left outer join patient p on p.hn=v.hn
left outer join lab_order lb on lb.lab_order_number=lh.lab_order_number
where  v.vstdate between '2010-02-01' and '2010-03-31'
and lb.lab_items_code ="76""91""92""102""91""92""102""103"


















รายงานผู้ป่วยทุกสิทธิ  BP  BMI ผลLAB
select v.hn,v.vstdate,v.cid,v.pttype,concat(p.pname,p.fname,"  ",p.lname) as ptname,o.bw,o.height,o.bpd,o.bps,
o.bmi,o.tg,o.hdl,o.bun,o.creatinine,o.ua,lb.lab_order_result as fbs
 from vn_stat  v
left outer join lab_head lh on lh.vn=v.vn
left outer join opdscreen o on o.vn=v.vn
left outer join patient p on p.hn=v.hn
left outer join lab_order lb on lb.lab_order_number=lh.lab_order_number
where  v.vstdate between '2010-02-01' and '2010-03-31'
and lb.lab_items_code ="76""77""78""79""91""92""193""405"