BMS-HOSxP Community
HOSxP => การเขียน SQL Script => ข้อความที่เริ่มโดย: treera ที่ สิงหาคม 28, 2009, 11:33:14 AM
-
สวัสดครับอาจารย์
ผมต้องการ sql ดึงข้อมูล lab ผู้ป่วยเบาหวาน
เช่น Bp,Fbs,Bun,Cr,Chorlesteral,Trigly.,HDL,LDL,MicroAlbumin
1 ปี ย้อนหลังแยกรายบุคคล
-
โรงพยาบาลอะไรครับ แนะนำด้วย ผมมีรายงานให้หลายตัวด้วยกำลังทำอยู่ 80 รายงาน
-
โรงพยาบาลอะไรครับ แนะนำด้วย ผมมีรายงานให้หลายตัวด้วยกำลังทำอยู่ 80 รายงาน
มีเยอะจัง ขอบ้างซิครับ ;D
-
ขอด้วยนะคะ สักไม่กี่รายงานก็เอาคะ
-
select h.lab_order_number, h.lab_receive_number, h.hn, h.order_date , h.order_time
,d.name as doctor_name, concat(pt.pname,pt.fname,' ',pt.lname) as ptname
,h.form_name , v.age_y as age_y , k.department as department_name
,lp.lab_perform_status_name
,(
select concat(l1.lab_order_result," ",case when l1.lab_order_result = "-" then "" else i1.lab_items_unit end) as result
from lab_order l1 left outer join lab_items i1 on i1.lab_items_code = l1.lab_items_code where l1.lab_order_number = h.lab_order_number and l1.lab_items_code = "76"
) as Glucose
,(
select concat(l2.lab_order_result," ",case when l2.lab_order_result = "-" then "" else i2.lab_items_unit end) as result
from lab_order l2 left outer join lab_items i2 on i2.lab_items_code = l2.lab_items_code where l2.lab_order_number = h.lab_order_number and l2.lab_items_code = "77"
) as BUN
,(
select concat(l3.lab_order_result," ",case when l3.lab_order_result = "-" then "" else i3.lab_items_unit end) as result
from lab_order l3 left outer join lab_items i3 on i3.lab_items_code = l3.lab_items_code where l3.lab_order_number = h.lab_order_number and l3.lab_items_code = "78"
) as Creatinine
,(
select concat(l4.lab_order_result," ",case when l4.lab_order_result = "-" then "" else i4.lab_items_unit end) as result
from lab_order l4 left outer join lab_items i4 on i4.lab_items_code = l4.lab_items_code where l4.lab_order_number = h.lab_order_number and l4.lab_items_code = "91"
) as HDL
,(
select concat(l5.lab_order_result," ",case when l5.lab_order_result = "-" then "" else i5.lab_items_unit end) as result
from lab_order l5 left outer join lab_items i5 on i5.lab_items_code = l5.lab_items_code where l5.lab_order_number = h.lab_order_number and l5.lab_items_code = "92"
) as LDL
,(
select concat(l6.lab_order_result," ",case when l6.lab_order_result = "-" then "" else i6.lab_items_unit end) as result
from lab_order l6 left outer join lab_items i6 on i6.lab_items_code = l6.lab_items_code where l6.lab_order_number = h.lab_order_number and l6.lab_items_code = "102"
) as Cholesterol
,(
select concat(l7.lab_order_result," ",case when l7.lab_order_result = "-" then "" else i7.lab_items_unit end) as result
from lab_order l7 left outer join lab_items i7 on i7.lab_items_code = l7.lab_items_code where l7.lab_order_number = h.lab_order_number and l7.lab_items_code = "103"
) as Triglyceride
from lab_head h
left outer join patient pt on pt.hn=h.hn
left outer join doctor d on d.code=h.doctor_code
left outer join vn_stat v on v.vn=h.vn
left outer join kskdepartment k on k.depcode = h.order_department
left outer join lab_perform_status lp on lp.lab_perform_status_id = h.lab_perform_status_id
where (year(h.order_date)+ 543) = "2552"
and k.depcode = "002"
order by h.order_date,h.order_time
แบบประมาณนี้เปล่าครับ ยังไงช่วยทดสอบด้วยน่ะครับ
k.depcode = "002" >> เป็นคลีนิคเบาหวาน ที่นี้ครับ