รายงานผู้ที่ได้รับการตรวจวัดสายตา ปีงบ 2558
รันแล้วส่งออกเป็น excel เอานะครับ
select p.cid,p.pname,p.fname,p.lname,
date_format(p.birthdate,'%Y-%m-%d') as birthday,
p.sex,h.address,
vill.village_moo as moo,
vill.village_code as aid,
'F' as unit,
CASE
when concat(e.r01,if(e.r04,e.r04,''))='20/20' then 14
when concat(e.r01,if(e.r04,e.r04,''))='20/30' then 6
when concat(e.r01,if(e.r04,e.r04,''))='20/40' then 5
when concat(e.r01,if(e.r04,e.r04,''))='20/50' then 4
when concat(e.r01,if(e.r04,e.r04,''))='20/70' then 3
when concat(e.r01,if(e.r04,e.r04,''))='20/100' then 2
when concat(e.r01,if(e.r04,e.r04,''))='20/200' then 1
when concat(e.r01,if(e.r04,e.r04,''))='15/200' then 17
when concat(e.r01,if(e.r04,e.r04,''))='10/200' then 15
when concat(e.r01,if(e.r04,e.r04,''))='5/200' then 16
when concat(e.r01,if(e.r04,e.r04,''))='15/200' then 17
when concat(e.r01,if(e.r04,e.r04,''))='FC3' then 7
when concat(e.r01,if(e.r04,e.r04,''))='FC2' then 8
when concat(e.r01,if(e.r04,e.r04,''))='FC1' then 9
when concat(e.r01,if(e.r04,e.r04,''))='HM' then 10
when concat(e.r01,if(e.r04,e.r04,''))='PJ' then 11
when concat(e.r01,if(e.r04,e.r04,''))='PL' then 12
when concat(e.r01,if(e.r04,e.r04,''))='NO PL' then 13
end as va_r,
CASE
when concat(e.l01,if(e.l04,e.l04,''))='20/20' then 14
when concat(e.l01,if(e.l04,e.l04,''))='20/30' then 6
when concat(e.l01,if(e.l04,e.l04,''))='20/40' then 5
when concat(e.l01,if(e.l04,e.l04,''))='20/50' then 4
when concat(e.l01,if(e.l04,e.l04,''))='20/70' then 3
when concat(e.l01,if(e.l04,e.l04,''))='20/100' then 2
when concat(e.l01,if(e.l04,e.l04,''))='20/200' then 1
when concat(e.l01,if(e.l04,e.l04,''))='15/200' then 17
when concat(e.l01,if(e.l04,e.l04,''))='10/200' then 15
when concat(e.l01,if(e.l04,e.l04,''))='5/200' then 16
when concat(e.l01,if(e.l04,e.l04,''))='15/200' then 17
when concat(e.l01,if(e.l04,e.l04,''))='FC3' then 7
when concat(e.l01,if(e.l04,e.l04,''))='FC2' then 8
when concat(e.l01,if(e.l04,e.l04,''))='FC1' then 9
when concat(e.l01,if(e.l04,e.l04,''))='HM' then 10
when concat(e.l01,if(e.l04,e.l04,''))='PJ' then 11
when concat(e.l01,if(e.l04,e.l04,''))='PL' then 12
when concat(e.l01,if(e.l04,e.l04,''))='NO PL' then 13
end as va_l,
date_format(v.vstdate,'%Y-%m-%d') as va_date,
(select hospitalcode from opdconfig) as hospcode
from person p,
vn_stat v,
house h,
village vill,
eye_screen e
where p.patient_hn=v.hn
and h.house_id=p.house_id and vill.village_id=h.village_id
and e.vn=v.vn
and v.vn=(select max(e1.vn) from vn_stat v1,eye_screen e1 where v1.hn=p.patient_hn and v1.vn=e1.vn and v1.vstdate between '2014-10-01' and '2015-09-30' )
select p.cid,p.pname,p.fname,p.lname,
date_format(p.birthdate,'%Y-%m-%d') as birthday,
p.sex,h.address,
vill.village_moo as moo,
vill.village_code as aid,
'F' as unit,
CASE
when concat(e.r01,if(e.r04,e.r04,''))='20/20' then 14
when concat(e.r01,if(e.r04,e.r04,''))='20/30' then 6
when concat(e.r01,if(e.r04,e.r04,''))='20/40' then 5
when concat(e.r01,if(e.r04,e.r04,''))='20/50' then 4
when concat(e.r01,if(e.r04,e.r04,''))='20/70' then 3
when concat(e.r01,if(e.r04,e.r04,''))='20/100' then 2
when concat(e.r01,if(e.r04,e.r04,''))='20/200' then 1
when concat(e.r01,if(e.r04,e.r04,''))='15/200' then 17
when concat(e.r01,if(e.r04,e.r04,''))='10/200' then 15
when concat(e.r01,if(e.r04,e.r04,''))='5/200' then 16
when concat(e.r01,if(e.r04,e.r04,''))='15/200' then 17
when concat(e.r01,if(e.r04,e.r04,''))='FC3' then 7
when concat(e.r01,if(e.r04,e.r04,''))='FC2' then 8
when concat(e.r01,if(e.r04,e.r04,''))='FC1' then 9
when concat(e.r01,if(e.r04,e.r04,''))='HM' then 10
when concat(e.r01,if(e.r04,e.r04,''))='PJ' then 11
when concat(e.r01,if(e.r04,e.r04,''))='PL' then 12
when concat(e.r01,if(e.r04,e.r04,''))='NO PL' then 13
end as va_r,
CASE
when concat(e.l01,if(e.l04,e.l04,''))='20/20' then 14
when concat(e.l01,if(e.l04,e.l04,''))='20/30' then 6
when concat(e.l01,if(e.l04,e.l04,''))='20/40' then 5
when concat(e.l01,if(e.l04,e.l04,''))='20/50' then 4
when concat(e.l01,if(e.l04,e.l04,''))='20/70' then 3
when concat(e.l01,if(e.l04,e.l04,''))='20/100' then 2
when concat(e.l01,if(e.l04,e.l04,''))='20/200' then 1
when concat(e.l01,if(e.l04,e.l04,''))='15/200' then 17
when concat(e.l01,if(e.l04,e.l04,''))='10/200' then 15
when concat(e.l01,if(e.l04,e.l04,''))='5/200' then 16
when concat(e.l01,if(e.l04,e.l04,''))='15/200' then 17
when concat(e.l01,if(e.l04,e.l04,''))='FC3' then 7
when concat(e.l01,if(e.l04,e.l04,''))='FC2' then 8
when concat(e.l01,if(e.l04,e.l04,''))='FC1' then 9
when concat(e.l01,if(e.l04,e.l04,''))='HM' then 10
when concat(e.l01,if(e.l04,e.l04,''))='PJ' then 11
when concat(e.l01,if(e.l04,e.l04,''))='PL' then 12
when concat(e.l01,if(e.l04,e.l04,''))='NO PL' then 13
end as va_l,
date_format(v.vstdate,'%Y-%m-%d') as va_date,
(select hospitalcode from opdconfig) as hospcode
from person p,
vn_stat v,
house h,
village vill,
eye_screen e
where p.patient_hn=v.hn
and h.house_id=p.house_id and vill.village_id=h.village_id
and e.vn=v.vn
and v.vn=(select max(e1.vn) from vn_stat v1,eye_screen e1 where v1.hn=p.patient_hn and v1.vn=e1.vn and v1.vstdate between '2014-10-01' and '2015-09-30' )
ความคิดเห็น
แสดงความคิดเห็น