select r.an,r.icode,r.mar_time,r.mar_date,concat(dr.name,' ',dr.strength ) as drname,dr.units,a.hn,
concat(pt.pname,' ',pt.fname,' ',pt.lname) as ptname,a.ward,w.name as wname,b.bedno
,r.drugusage,pty.name as pttype,r.med_rx_number,concat(du.name1," ",du.name2," ",du.name3) as dd
,r.qty,r.med_interval_type_id,met.interval_name as inname,r.orderstatus,sp.name1,sp.name2
,sp.name3,dr.ename,med.orderdate,med.offdate
,pt.drugallergy,dr.units , i.entry_staff , doc.name as stname, a.age_y, a.age_m, a.age_d, k.prediag,du.shortlist
,os.bw ,os.height,r.sp_use,if(sp.name1 is not null ,sp.name1,du.common_name) as drugusename1
,if(sp.name2 is not null,sp.name2,'') as drugusename2,if(sp.name3 is not null,sp.name3,'')as drugusename3
,(k.bw/1000) as bw_1 ,med.med_plan_number,
pt.birthday,now()as today,dr.hintcode_eng,med.orderdate,
timestampdiff(year,pt.birthday,curdate()) as count_year,
timestampdiff(month,pt.birthday,curdate())-(timestampdiff(year,pt.birthday,curdate())*12) as count_month,
timestampdiff(day,date_add(pt.birthday,interval (timestampdiff(month,pt.birthday,curdate())) month),curdate()) as count_day
from ipd_mar r
left outer join drugitems dr on dr.icode=r.icode
left outer join drugusage du on du.drugusage=r.drugusage
left outer join an_stat a on a.an=r.an
left outer join patient pt on pt.hn=a.hn
left outer join ward w on w.ward=a.ward
left outer join iptadm b on b.an=r.an
left outer join pttype pty on pty.pttype=a.pttype
left outer join med_interval_type met on met.med_interval_type_id=r.med_interval_type_id
left outer join sp_use sp on sp.sp_use=r.sp_use
left outer join ipt_order_no i on i.order_no = r.med_rx_number
left outer join doctor doc on doc.code = i.entry_staff
left outer join ipt k on k.an=r.an
left outer join opdscreen os on os.vn=a.vn
left outer join medpay_ipd medp on medp.icode = r.icode and medp.med_rx_number = r.med_rx_number
and medp.drugusage = r.drugusage and medp.sp_use = r.sp_use
left outer join medplan_ipd med on med.icode=r.icode and med.med_plan_number = medp.med_plan_number
where r.med_rx_number="90089" and r.icode <'2000000' and r.icode not in('1900273','1900274','1900623')
order by r.orderstatus
อันนี้รองดู
ลองดูแล้วไม่ได้ค่ะ โค้ดเดิมเป็นแบบนี้นะคะ (แต่พอดีในตาราง ipd_mar ฟิลด์ drugusage กับ sp_use มีข้อมูลไม่ครบค่ะ) เลยอยากจะดึงวิธีใช้ยาจากตาราง opitemrece แทน จะแก้ตรงไหนบ้างคะ?
select r.an,r.icode,r.mar_time,r.mar_date,concat(dr.name,' ',dr.strength ) as drname,dr.units,a.hn,
concat(pt.pname,' ',pt.fname,' ',pt.lname) as ptname,a.ward,w.name as wname,b.bedno
,r.drugusage,pty.name as pttype,r.med_rx_number,du.name1,du.name2,du.name3
,r.qty,r.med_interval_type_id,met.interval_name as inname,r.orderstatus,sp.name1,sp.name2
,sp.name3,dr.name,med.orderdate,med.offdate
,pt.drugallergy,dr.units , i.entry_staff , doc.name as stname, a.age_y, a.age_m, a.age_d, k.prediag
,os.bw ,os.height,if(sp.name1 is not null ,sp.name1,du.common_name) as drugusename1
,if(sp.name2 is not null,sp.name2,'') as drugusename2,if(sp.name3 is not null,sp.name3,'')as drugusename3
,(k.bw/1000) as bw_1 ,med.med_plan_number,
pt.birthday,now()as today,dr.hintcode_eng,med.orderdate,
timestampdiff(year,pt.birthday,curdate()) as count_year,
timestampdiff(month,pt.birthday,curdate())-(timestampdiff(year,pt.birthday,curdate())*12) as count_month,
timestampdiff(day,date_add(pt.birthday,interval (timestampdiff(month,pt.birthday,curdate())) month),curdate()) as count_day
from ipd_mar r
left outer join drugitems dr on dr.icode=r.icode
left outer join drugusage du on du.drugusage=r.drugusage
left outer join an_stat a on a.an=r.an
left outer join patient pt on pt.hn=a.hn
left outer join ward w on w.ward=a.ward
left outer join iptadm b on b.an=r.an
left outer join pttype pty on pty.pttype=a.pttype
left outer join med_interval_type met on met.med_interval_type_id=r.med_interval_type_id
left outer join sp_use sp on sp.sp_use=r.sp_use
left outer join ipt_order_no i on i.order_no = r.med_rx_number
left outer join doctor doc on doc.code = i.entry_staff
left outer join ipt k on k.an=r.an
left outer join opdscreen os on os.vn=a.vn
left outer join medpay_ipd medp on medp.icode = r.icode and medp.med_rx_number = r.med_rx_number
and medp.drugusage = r.drugusage and medp.sp_use = r.sp_use
left outer join medplan_ipd med on med.icode=r.icode and med.med_plan_number = medp.med_plan_number
where r.med_rx_number='895654' and r.icode <'2000000' and r.icode not in('1900273','1900274','1900623')
order by r.orderstatus
หัวข้อ: ขอความช่วยเหลือค่ะ...ปรับแก้IPD-MAR (อ่าน 8367 ครั้ง)