1
Report Exchange / ขอความช่วยเหลือค่ะ...ปรับแก้IPD-MAR
« เมื่อ: กรกฎาคม 02, 2013, 10:32:31 AM »
รบกวนอาจารย์ทุกท่านด้วยนะคะ จะปรับแก้รายงานในส่วนของ drugusage ให้ดึงมาจากตาราง 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,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(du.name1 is not null ,du.name1,du.common_name) as drugusename1
,if(du.name2 is not null,du.name2,'') as drugusename2,if(du.name3 is not null,du.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 an_stat a on a.an=r.an
left outer join opitemrece op on op.an=a.an
left outer join drugusage du on du.drugusage=op.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 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
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
จะต้องปรับแก้ตรงไหนบ้างคะ (ลองปรับหลายรอบแล้วค่ะ ยังไม่ได้)
โค้ดตามนี้ค่ะ..
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,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(du.name1 is not null ,du.name1,du.common_name) as drugusename1
,if(du.name2 is not null,du.name2,'') as drugusename2,if(du.name3 is not null,du.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 an_stat a on a.an=r.an
left outer join opitemrece op on op.an=a.an
left outer join drugusage du on du.drugusage=op.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 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
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