2
« เมื่อ: กุมภาพันธ์ 08, 2016, 15:52:42 PM »
set @d1 = '2012-07-01';
set @d2 = '2012-07-31';
set @dm_clinic_code = (select sys_value from sys_var where sys_name="ht_clinic_code" limit 1) ;
set @lab_items_code = (select lab_items_code from lab_items where lab_items_name=(select sys_value from sys_var where sys_name="lab_link_cr" limit 1)) ;
select *
from
(
select v.hn,concat(p.pname,p.fname,space(3),p.lname) as ptname, v.aid
,
(select lh3.order_date from lab_order lo3
left outer join lab_head lh3 on lh3.lab_order_number=lo3.lab_order_number
where lo3.lab_items_code=@lab_items_code and lh3.hn=v.hn
order by lh3.report_date desc limit 2,1) as Cr_date3
,
(select lo3.lab_order_result from lab_order lo3
left outer join lab_head lh3 on lh3.lab_order_number=lo3.lab_order_number
where lo3.lab_items_code=@lab_items_code and lh3.hn=v.hn
order by lh3.report_date desc limit 2,1) as Cr3
,
(select (case when p.sex=2 then case
when lo3.lab_order_result <= 0.7 then 144 * pow((lo3.lab_order_result/0.7), -0.329) * pow(0.993, v.age_y)
when lo3.lab_order_result >= 0.7 then 144 * pow((lo3.lab_order_result/0.7), -1.209) * pow(0.993, v.age_y)
end
when p.sex=1 then case
when lo3.lab_order_result <= 0.9 then 141 * pow((lo3.lab_order_result/0.9), -0.411) * pow(0.993, v.age_y)
when lo3.lab_order_result >= 0.9 then 141 * pow((lo3.lab_order_result/0.9), -1.209) * pow(0.993, v.age_y)
end
end)
from lab_order lo3
left outer join lab_head lh3 on lh3.lab_order_number=lo3.lab_order_number
where lo3.lab_items_code=@lab_items_code and lh3.hn=v.hn
order by lh3.report_date desc limit 2,1) as GFR3
,
(select lh2.order_date from lab_order lo2
left outer join lab_head lh2 on lh2.lab_order_number=lo2.lab_order_number
where lo2.lab_items_code=@lab_items_code and lh2.hn=v.hn
order by lh2.report_date desc limit 1,1) as Cr_date2
,
(select lo2.lab_order_result from lab_order lo2
left outer join lab_head lh2 on lh2.lab_order_number=lo2.lab_order_number
where lo2.lab_items_code=@lab_items_code and lh2.hn=v.hn
order by lh2.report_date desc limit 1,1) as Cr2
,
(select (case when p.sex=2 then case
when lo2.lab_order_result <= 0.7 then 144 * pow((lo2.lab_order_result/0.7), -0.329) * pow(0.993, v.age_y)
when lo2.lab_order_result >= 0.7 then 144 * pow((lo2.lab_order_result/0.7), -1.209) * pow(0.993, v.age_y)
end
when p.sex=1 then case
when lo2.lab_order_result <= 0.9 then 141 * pow((lo2.lab_order_result/0.9), -0.411) * pow(0.993, v.age_y)
when lo2.lab_order_result >= 0.9 then 141 * pow((lo2.lab_order_result/0.9), -1.209) * pow(0.993, v.age_y)
end
end)
from lab_order lo2
left outer join lab_head lh2 on lh2.lab_order_number=lo2.lab_order_number
where lo2.lab_items_code=@lab_items_code and lh2.hn=v.hn
order by lh2.report_date desc limit 1,1) as GFR2
,
(select lh.order_date from lab_order lo
left outer join lab_head lh on lh.lab_order_number=lo.lab_order_number
where lo.lab_items_code=@lab_items_code and lh.hn=v.hn
and lh.order_date between @d1 and @d2
order by lh.report_date desc limit 0,1) as Cr_date1
,
(select lo.lab_order_result from lab_order lo
left outer join lab_head lh on lh.lab_order_number=lo.lab_order_number
where lo.lab_items_code=@lab_items_code and lh.hn=v.hn
and lh.order_date between @d1 and @d2
order by lh.report_date desc limit 0,1) as Cr1
,
(select (case when p.sex=2 then case
when lo.lab_order_result <= 0.7 then 144 * pow((lo.lab_order_result/0.7), -0.329) * pow(0.993, v.age_y)
when lo.lab_order_result >= 0.7 then 144 * pow((lo.lab_order_result/0.7), -1.209) * pow(0.993, v.age_y)
end
when p.sex=1 then case
when lo.lab_order_result <= 0.9 then 141 * pow((lo.lab_order_result/0.9), -0.411) * pow(0.993, v.age_y)
when lo.lab_order_result >= 0.9 then 141 * pow((lo.lab_order_result/0.9), -1.209) * pow(0.993, v.age_y)
end
end)
from lab_order lo
left outer join lab_head lh on lh.lab_order_number=lo.lab_order_number
where lo.lab_items_code=@lab_items_code and lh.hn=v.hn
and lh.order_date between @d1 and @d2
order by lh.report_date desc limit 0,1) as GFR1
from vn_stat v
left outer join patient p on p.hn = v.hn
where v.vstdate between @d1 and @d2 and
p.chwpart=38 and
p.amppart=05 and
v.hn in (select hn from clinicmember where clinic=@dm_clinic_code) and
(select lo.lab_order_result from lab_order lo
left outer join lab_head lh on lh.lab_order_number=lo.lab_order_number
where lo.lab_items_code=@lab_items_code and lh.hn=v.hn
and lh.order_date between @d1 and @d2
order by lh.report_date desc limit 0,1) > 0
group by v.hn
)
as ttemp
แสดงกระทู้
และสาเหตุน่าจะเป็นเพราะอะไรครับ
