สอบถามรายงาน CrCl

[niyomstd]

ช่วยตรวจสอบคำสั่งนี้หน่อยครับ ไม่รู้ว่าดึงข้อมูลมาถูกหรือเปล่า ทั้งๆที่ มีการบันทึก น้ำหนัก ส่วนสูง ในระบบ
แต่กลับ ดีงข้อมูลมาไม่ครบในส่วนของ BW บางราย
set @d1 = '2011-10-01';
set @d2 = '2012-04-31';

set @dm_clinic_code = (select sys_value from sys_var where sys_name="dm_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,"  ",p.lname) as ptname, v.cid,v.sex,v.age_y,opd.bw,p.informaddr,v.pdx,cl.name ,c.begin_year
,

(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,lh2.report_time 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,lh2.report_time desc limit 1,1) as Cr2
,
(select if (p.sex='2',(((140-v.age_y)* opd.bw * 0.85)/(72*lo2.lab_order_result)),(((140-v.age_y)* opd.bw )/(72*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,lh2.report_time desc limit 1,1) as CrCl2
,
(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 olh.report_date desc,lh.report_time 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,lh.report_time desc limit 0,1) as Cr1
,
(select if (p.sex='2',(((140-v.age_y)* opd.bw * 0.85)/(72*lo.lab_order_result)),(((140-v.age_y)* opd.bw )/(72*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,lh.report_time desc limit 0,1) as CrCl

from vn_stat v
left outer join patient p on p.hn = v.hn
left outer join opdscreen opd on opd.vn=v.vn
left outer join clinicmember c on c.hn=p.hn
left outer join clinic cl on cl.clinic=c.clinic
where v.vstdate between @d1 and @d2 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 opd.bw desc ,lh.report_date  desc,lh.report_time desc limit 0,1)  > 0
group by v.hn
)  as ttemp

[niyomstd]

ตรวจสอบกันแล้วเป็นอย่างไรบ้างครับ