script คำนวนค่า eGFR ของผล Creatinine ( Cr ) เอาไปปรับที่รายงาน Form-LABREPORT1 ใช้ที่ รพ.ภูหลวงแล้วครับ ======>>
select concat(p.pname,p.fname," ",p.lname) as ptname,
d.name as request_doctor_name,
h.*,l.*,i.lab_items_unit,i.lab_items_normal_value ,i.lab_items_name,concat(d2.name)as name1,concat(d3.name)as name2 ,
i.*,CASE WHEN l.lab_items_code = (select lab_items_code from lab_items where lab_items_name = (select sys_value from sys_var where sys_name like '%lab_link_cr%')
) and p.sex = 2 and l.lab_order_result > 0.7 THEN
concat("eGFR = ",round((144*((pow((l.lab_order_result/0.7), -1.209)) * (pow(0.993,(YEAR(now())- YEAR(p.birthday)))))),2))
WHEN l.lab_items_code = (select lab_items_code from lab_items where lab_items_name = (select sys_value from sys_var where sys_name like '%lab_link_cr%')
) and p.sex = 2 and (l.lab_order_result < 0.7 or l.lab_order_result = 0.7 ) THEN
concat("eGFR = ",round((144*((pow((l.lab_order_result/0.7), -0.329)) * (pow(0.993,(YEAR(now())- YEAR(p.birthday)))))),2))
WHEN l.lab_items_code = (select lab_items_code from lab_items where lab_items_name = (select sys_value from sys_var where sys_name like '%lab_link_cr%')
) and p.sex = 1 and l.lab_order_result > 0.9 THEN
concat("eGFR = ",round((141*((pow((l.lab_order_result/0.9), -1.209)) * (pow(0.993,(YEAR(now())- YEAR(p.birthday)))))),2))
WHEN l.lab_items_code = (select lab_items_code from lab_items where lab_items_name = (select sys_value from sys_var where sys_name like '%lab_link_cr%')
) and p.sex = 1 and (l.lab_order_result < 0.9 or l.lab_order_result = 0.9 ) THEN
concat("eGFR = ",round((141*((pow((l.lab_order_result/0.9), -0.411)) * (pow(0.993,(YEAR(now())- YEAR(p.birthday)))))),2))
END as "eGRF"
from lab_head h
left outer join lab_order l on l.lab_order_number=h.lab_order_number and l.lab_order_result IS NOT NULL and l.lab_order_result<>''
left outer join lab_items i on i.lab_items_code=l.lab_items_code
left outer join patient p on p.hn=h.hn
left outer join doctor d on d.code=h.doctor_code
left outer join opduser d2 on d2.loginname=h.reporter_name
left outer join opduser d3 on d3.loginname=h.approve_staff
where h.lab_order_number = 239
order by i.display_order,i.lab_items_name
left outer join lab_order l on l.lab_order_number=h.lab_order_number and l.lab_order_result IS NOT NULL and l.lab_order_result<>''
left outer join lab_items i on i.lab_items_code=l.lab_items_code
left outer join patient p on p.hn=h.hn
left outer join doctor d on d.code=h.doctor_code
left outer join opduser d2 on d2.loginname=h.reporter_name
left outer join opduser d3 on d3.loginname=h.approve_staff
where h.lab_order_number = 239
order by i.display_order,i.lab_items_name
Credit : +อำนวย หาแก้ว รพ.ผาขาว จ.เลย