9
« เมื่อ: มกราคม 10, 2014, 10:53:20 AM »
พอดีพี่พยาบาลมาขอข้อมูลไปใช้เกี่ยวกับคนไข้เบาหวานความดันครับ ในส่วนที่ทำสีแดงใว้ ผมต้องการหาจำนวนยาที่คนไข้ได้รับในแต่ละครั้ง และให้คะแนนไปด้วย แต่บาง visit คนไข้ไม่ได้รับยาที่เกี่ยวข้อง แต่ผลกับบอกว่าได้รับยา 1 ตัว และถ้าได้รับยาที่เกี่ยวข้อง ผลกับบอกว่าได้รับยา 2 ตัว พี่ๆ ช่วยชี้แนะด้วยครับ
select o.vstdate,o.hn,concat(p.pname,p.fname," ",p.lname) as ptname,p.birthday,concat(year(curdate()) - year(p.birthday)) as year,p.addrpart,p.moopart,t.full_name,c.begin_year,
v.pdx,v.dx0,v.dx1,v.dx2,o.bmi,o.bps,o.bpd,o.fbs,o.hba1c,o.creatinine,micro_albumin,o.advice3,o.advice5,o.advice7,
e2.dmht_eye_screen_result_name,e3.dmht_eye_screen_result_name,f2.dmht_foot_screen_result_name,f3.dmht_foot_screen_result_name,
if(count(DISTINCT (drugitems.`name` LIKE "metformi%"or drugitems.`name` LIKE "GLIBENCLAMIDE%"or drugitems.`name` LIKE "GLIPIZIDE%"or drugitems.`name` LIKE "INSULIN%"))=1,40,null) as Drug_dm_1,
if(count(DISTINCT (drugitems.`name` LIKE "metformi%"or drugitems.`name` LIKE "GLIBENCLAMIDE%"or drugitems.`name` LIKE "GLIPIZIDE%"or drugitems.`name` LIKE "INSULIN%"))=2,30,null) as Drug_dm_2,
if(count(DISTINCT (drugitems.`name` LIKE "metformi%"or drugitems.`name` LIKE "GLIBENCLAMIDE%"or drugitems.`name` LIKE "GLIPIZIDE%"or drugitems.`name` LIKE "INSULIN%"))=3,20,null) as Drug_dm_3,
if(count(DISTINCT (drugitems.`name` LIKE "metformi%"or drugitems.`name` LIKE "GLIBENCLAMIDE%"or drugitems.`name` LIKE "GLIPIZIDE%"or drugitems.`name` LIKE "INSULIN%"))=4,10,null) as Drug_dm_4,
if(count(DISTINCT (drugitems.`name` LIKE "ENALAPRIL%" or drugitems.`name` LIKE "AMLODIPINE%"or drugitems.`name` LIKE "ATENOLOL%"or drugitems.`name` LIKE "HYDROCHLOROTHIAZIDE%" or drugitems.`name` LIKE "propranolol%"or drugitems.`name` LIKE "NIFEDIPINE%"or drugitems.`name` LIKE "LOSARTAN%"or drugitems.`name` LIKE "METOPROLOL%"or drugitems.`name` LIKE "MODURETIC%"or drugitems.`name` LIKE "DOXAZOSIN%"))=1,40,null) as Drug_ht_1,
if(count(DISTINCT (drugitems.`name` LIKE "ENALAPRIL%" or drugitems.`name` LIKE "AMLODIPINE%"or drugitems.`name` LIKE "ATENOLOL%"or drugitems.`name` LIKE "HYDROCHLOROTHIAZIDE%" or drugitems.`name` LIKE "propranolol%"or drugitems.`name` LIKE "NIFEDIPINE%"or drugitems.`name` LIKE "LOSARTAN%"or drugitems.`name` LIKE "METOPROLOL%"or drugitems.`name` LIKE "MODURETIC%"or drugitems.`name` LIKE "DOXAZOSIN%"))=2,30,null) as Drug_ht_2,
if(count(DISTINCT (drugitems.`name` LIKE "ENALAPRIL%" or drugitems.`name` LIKE "AMLODIPINE%"or drugitems.`name` LIKE "ATENOLOL%"or drugitems.`name` LIKE "HYDROCHLOROTHIAZIDE%" or drugitems.`name` LIKE "propranolol%"or drugitems.`name` LIKE "NIFEDIPINE%"or drugitems.`name` LIKE "LOSARTAN%"or drugitems.`name` LIKE "METOPROLOL%"or drugitems.`name` LIKE "MODURETIC%"or drugitems.`name` LIKE "DOXAZOSIN%"))=3,20,null) as Drug_ht_3,
if(count(DISTINCT (drugitems.`name` LIKE "ENALAPRIL%" or drugitems.`name` LIKE "AMLODIPINE%"or drugitems.`name` LIKE "ATENOLOL%"or drugitems.`name` LIKE "HYDROCHLOROTHIAZIDE%" or drugitems.`name` LIKE "propranolol%"or drugitems.`name` LIKE "NIFEDIPINE%"or drugitems.`name` LIKE "LOSARTAN%"or drugitems.`name` LIKE "METOPROLOL%"or drugitems.`name` LIKE "MODURETIC%"or drugitems.`name` LIKE "DOXAZOSIN%"))=4,10,null) as Drug_ht_4,v.income
from opdscreen o
left outer join patient p on o.hn=p.hn
left outer join thaiaddress t on t.addressid=concat(p.chwpart,p.amppart,p.tmbpart)
left outer join vn_stat v on o.vn=v.vn
left outer join clinicmember c on v.hn=c.hn
left outer join clinicmember_cormobidity_screen c1 on v.vn=c1.vn
left outer join clinicmember_cormobidity_eye_screen e1 on c1.clinicmember_cormobidity_screen_id=e1.clinicmember_cormobidity_screen_id
left outer join clinicmember_cormobidity_foot_screen f1 on c1.clinicmember_cormobidity_screen_id=f1.clinicmember_cormobidity_screen_id
left outer join dmht_eye_screen_result e2 on e1.dmht_eye_screen_result_left_id=e2.dmht_eye_screen_result_id
left outer join dmht_eye_screen_result e3 on e1.dmht_eye_screen_result_right_id=e3.dmht_eye_screen_result_id
left outer join dmht_foot_screen_result f2 on f1.dmht_foot_screen_result_left_id=f2.dmht_foot_screen_result_id
left outer join dmht_foot_screen_result f3 on f1.dmht_foot_screen_result_right_id=f3.dmht_foot_screen_result_id
Left Outer Join opitemrece ON v.vn = opitemrece.vn
Left Outer Join drugitems ON opitemrece.icode= drugitems.icode
where o.vstdate between "2013-10-01" and "2014-09-30"
and v.pdx = "I10"
group by v.vn
order by t.full_name,o.hn,o.vstdate
แสดงกระทู้
