begin
GetDateRangeDialog(d1, d2);
ds1:=FormatDateTime('yyyy-mm-dd',d1);
ds2:=FormatDateTime('yyyy-mm-dd',d2);
clinicname:= getpickuplist('select name from clinic where chronic="Y" order by name');
clinic:=getsqlstringdata('select clinic from clinic where name="'+clinicname+'"');
clinic_hn := getsqlsubquerydata('select hn from clinicmember where clinic="'+clinic+'"');
clinic_hn := getsqlsubquerydata('select hn from ovst where vstdate between "'+ds1+'" and "'+ds2+'" and hn in ('+clinic_hn+') ');
ckd_level_name:= getpickuplist('select clinic_cormobidity_ckd_name from clinic_cormobidity_ckd order by clinic_cormobidity_ckd_id');
ckd_min:=getsqlstringdata('select clinic_cormobidity_ckd_min from clinic_cormobidity_ckd where clinic_cormobidity_ckd_name="'+ckd_level_name+'"');
ckd_max:=getsqlstringdata('select clinic_cormobidity_ckd_max from clinic_cormobidity_ckd where clinic_cormobidity_ckd_name="'+ckd_level_name+'"');
ChangeReportSQL('select concat(pt.pname,pt.fname," ",pt.lname) as ptname ,pt.sex,pt.birthday,'
+'pt.cid as pid ,pc.cardno,v.pdx as icd_pdx,v.dx0 as icd_sdx,v.dx1 as icd_sdx1,v.dx2 as icd_sdx2, '
+'v.hn,v.pcode ,lo.lab_items_code,lo.lab_order_result,lh.order_date, '
+'if(pt.sex=1,1,0.742) as factor , '
{
begin
if sex=2 then
begin
if LastCr>0.7 then
gfr:=(144*(exp(-1.209*ln(LastCr/0.7)))*exp(age_y*ln(0.993)))
else
gfr:=(144*(exp(-0.329*ln(LastCr/0.7)))*exp(age_y*ln(0.993)))
end
else
begin
if LastCr>0.9 then
gfr:=(141*(exp(-1.209*ln(LastCr/0.9)))*exp(age_y*ln(0.993)))
else
gfr:=(144*(exp(-0.411*ln(LastCr/0.9)))*exp(age_y*ln(0.993)))
end ;
end; }
{ +'(144*exp(if(pt.sex=2,-0.329,-0.411)*ln(lo.lab_order_result/0.9))*exp(v.age_y*ln(0.993)) as gfr ' }
+'(186*exp(-1.154*ln(lo.lab_order_result))*exp(-0.203*ln(v.age_y))*if(pt.sex=1,1,0.742) ) as gfr '
+'from vn_stat v '
+'left outer join patient pt on pt.hn=v.hn '
+'left outer join ptcardno pc on pc.hn=v.hn and pc.cardtype="01" '
+'left outer join clinicmember c on c.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 '
+'left outer join pttype p on p.pttype=pt.pttype '
+'where lo.lab_items_code in (select lab_items_code from lab_items where lab_items_name= '
+'(select sys_value from sys_var where sys_name="lab_link_cr" )) '
+'and v.vstdate between "'+ds1+'" and "'+ds2+'" '
+'and v.hn in ('+clinic_hn+') '
+'and (186*exp(-1.154*ln(lo.lab_order_result))*exp(-0.203*ln(v.age_y))*if(pt.sex=1,1,0.742) ) between "'+ckd_min+'" and "'+ckd_max+'" '
{ +'and (144*exp(if(pt.sex=2,-0.329,-0.411)*ln(lo.lab_order_result/0.9))*exp(v.age_y*ln(0.993) ) between "'+ckd_min+'" and "'+ckd_max+'" ' }
+'group by v.hn ');
end;
คือผมอยากทราบวิธีการเขียนคำสั่งจาก ในกรอบสีแดงเป็นตรงที่สีน้ำเงินครับ พอดีแก้ report รายงานโรคไต แยกตาม clinic เก่าที่มีในเครื่อง ครับพอดีจะใช้lสูตรคำนวณ CKD-EPI ลองแก้เป็นแบบตรงสีเขียวแล้วรายงานไม่ออกครับ
ขอบคุณครับ
หัวข้อ: ช่วยแก้ไข คำสั่ง SQL ในรายงาน CKD หน่อยครับ (อ่าน 8275 ครั้ง)