โรงพยาบาลจอมพระ

select p.sex,p.age_y,o.hba1c,o.bw,o.bmi,round(o.creatinine_kidney_percent,2),o.creatinine,
round(
(case when p.sex=1 then (((140-p.age_y)*o.bw)/(72*o.creatinine))
else (((140-p.age_y)*o.bw*0.85)/(72*o.creatinine)) end),2)as GFR_CKD_EPI
,
round(if(p.sex=1,(((140-p.age_y)*o.bw)/(72*o.creatinine)),(((140-p.age_y)*o.bw*0.85)/(72*o.creatinine))),2)as K,

(case when if(p.sex=1,(((140-p.age_y)*o.bw)/(72*o.creatinine)),
((140-p.age_y)*o.bw*0.85)/(72*o.creatinine))>89 then ‘ STATE 1’
when if(p.sex=1,(((140-p.age_y)*o.bw)/(72*o.creatinine)),
((140-p.age_y)*o.bw*0.85)/(72*o.creatinine)) between ‘60.00’ and ‘89.999’ then ‘ STATE 2’
when if(p.sex=1,(((140-p.age_y)*o.bw)/(72*o.creatinine)),
((140-p.age_y)*o.bw*0.85)/(72*o.creatinine)) between ‘30.00’ and ‘59.999’ then ‘ STATE 3’
when if(p.sex=1,(((140-p.age_y)*o.bw)/(72*o.creatinine)) ,
((140-p.age_y)*o.bw*0.85)/(72*o.creatinine)) between ‘15.00’ and ‘29.99’ then ‘ STATE 4’
when if(p.sex=1,(((140-p.age_y)*o.bw)/(72*o.creatinine)),
((140-p.age_y)*o.bw*0.85)/(72*o.creatinine)) between ‘0.01’ and ‘14.999’ then ‘ STATE 5’
else ‘error massage’ end)as KK,

c.clinic,concat(v.pdx,’ ‘,v.dx0,’ ‘,v.dx1,’ ‘,v.dx2,’ ‘,v.dx3,’ ‘,v.dx4,’ ‘,v.dx5)as diag,ROUND(o.bmi,2)as BMI,
(case when o.creatinine_kidney_percent>=90 then ‘1’
when o.creatinine_kidney_percent between ‘60.00’ and ‘89.999’ then ‘2’
when o.creatinine_kidney_percent between ‘30.00’ and ‘59.999’ then ‘3’
when o.creatinine_kidney_percent between ‘15.00’ and ‘29.999’ then ‘4’
when o.creatinine_kidney_percent<=15 then ‘5’
else ‘error massage’ end)as GFR,

o.creatinine_kidney_percent,o.vn,o.hn,o.vstdate,o.vsttime,o.cc,o.symptom,
p.cid,concat(p.pname,p.fname,’ ‘,p.lname)as name,(case when p.sex=’1’ then ‘ชาย’ else ‘หญิง’ end) as sex,
p.age_y
from opdscreen o
left join person p on p.patient_hn=o.hn
left join vn_stat v on v.vn=o.vn
left join clinicmember c on c.hn=o.hn
where (((o.vstdate between ‘2015-10-01’ and ‘2015-12-31’)
and (o.creatinine_kidney_percent is not null and o.creatinine_kidney_percent<>”0.00″) and o.creatinine<>0.00)
and ((v.pdx between”e100″ and “e149″
or v.dx0 between”e100” and “e149″
or v.dx1 between”e100” and “e149″
or v.dx2 between”e100” and “e149″
or v.dx3 between”e100” and “e149″
or v.dx4 between”e100” and “e149″
or v.dx5 between”e100” and “e149″) or
(v.pdx between”i10” and “i152″
or v.dx0 between”i10” and “i152″
or v.dx1 between”i10” and “i152″
or v.dx2 between”i10” and “i152″
or v.dx3 between”i10” and “i152″
or v.dx4 between”i10” and “i152″
or v.dx5 between”i10” and “i152”)))
and o.hba1c is not null

group by o.hn desc
order by KK,o.hba1c asc