BMS-HOSxP Community
HOSxP => Report Exchange => ข้อความที่เริ่มโดย: autobotz ที่ สิงหาคม 17, 2012, 09:59:13 AM
-
จากคำสั่ง SQL ด้านล่างถ้าจะแก้ไขให้ถูกควรแก้ตรงไหนครับ ???
รบกวนทุก ๆ ท่านด้วยนะครับ ;D
select v.vstdate,v.hn,concat(p.pname,p.fname," ",p.lname)as ptname,op.bmi,op.bps,op.bpd,
(select lo.lab_order_result from lab_order lo
where lo.lab_items_name_ref like '%Hb A1C') as HbA1C,
(select lo.lab_order_result from lab_order lo
where lo.lab_items_name_ref like 'Glucose') as Glucose
from vn_stat v
left outer join opdscreen op on op.vn = v.vn
left outer join patient p on p.hn = v.hn
left outer join lab_head lh on lh.vn = v.vn
left outer join lab_order lo on lo.lab_order_number = lh.lab_order_number
where v.vstdate between '2012-01-01' and '2012-01-31'
and (lo.lab_items_name_ref like '%Hb A1C' or lo.lab_items_name_ref like 'Glucose')
and lh.vn <> ""
and lo.lab_order_result <>""
and op.bmi <>""
and op.bps <>""
and op.bpd <>""
and v.age_y >= '65'
group by ptname
order by lo.lab_items_name_ref
-
ไม่รู้ว่าต้องการแนวนี้หรือเปล่าก็ลองเอาไปปรับดูนะครับ
select v.vstdate,v.hn,concat(p.pname,p.fname," ",p.lname)as ptname,op.bmi,op.bps,op.bpd,
lo_g.lab_order_result as Glucose,lo_h.lab_order_result as HbA1C
from vn_stat v
left outer join opdscreen op on op.vn = v.vn
left outer join patient p on p.hn = v.hn
left outer join lab_head lh on lh.vn = v.vn
left outer join lab_order lo_g on lo_g.lab_order_number = lh.lab_order_number and lo_g.lab_items_name_ref like '%Glucose%'
left outer join lab_order lo_h on lo_h.lab_order_number = lh.lab_order_number and ((lo_h.lab_items_name_ref like '%Hb A1C%') or (lo_h.lab_items_name_ref like '%HbA1c%'))
where v.vstdate between '2012-01-01' and '2012-01-31'
and lh.vn <> ""
and lo_g.lab_order_result <>""
and lo_h.lab_order_result <>""
and op.bmi <>""
and op.bps <>""
and op.bpd <>""
and v.age_y >= '65'
group by ptname
-
ไม่รู้ว่าต้องการแนวนี้หรือเปล่าก็ลองเอาไปปรับดูนะครับ
select v.vstdate,v.hn,concat(p.pname,p.fname," ",p.lname)as ptname,op.bmi,op.bps,op.bpd,
lo_g.lab_order_result as Glucose,lo_h.lab_order_result as HbA1C
from vn_stat v
left outer join opdscreen op on op.vn = v.vn
left outer join patient p on p.hn = v.hn
left outer join lab_head lh on lh.vn = v.vn
left outer join lab_order lo_g on lo_g.lab_order_number = lh.lab_order_number and lo_g.lab_items_name_ref like '%Glucose%'
left outer join lab_order lo_h on lo_h.lab_order_number = lh.lab_order_number and ((lo_h.lab_items_name_ref like '%Hb A1C%') or (lo_h.lab_items_name_ref like '%HbA1c%'))
where v.vstdate between '2012-01-01' and '2012-01-31'
and lh.vn <> ""
and lo_g.lab_order_result <>""
and lo_h.lab_order_result <>""
and op.bmi <>""
and op.bps <>""
and op.bpd <>""
and v.age_y >= '65'
group by ptname
ได้ตามที่คาดหวังครับ ขอบคุณมากครับ ;D
-
ใช้แค่ like "%a1c%" ก็ได้ครับ ไม่ต้องใช้ 2 เงื่อนไข