ดู Code ผู้มารับบริการทันตกรรมให้หน่อยครับ

[prapansak]

Code ที่ใช้นำไปรันใน SQL hosxp ผ่านครับ แต่พอเอามาใส่รายงาน ฟ้องว่า vn_stat v on v.vn=d.vn ซ้ำกับ vn_stat v1 on v1.vn=d.vn บอกวิธีแก้ให้หน่อยครับ ขอบคุณครับ

 ChangeReportSQL('select v.hospsub,h.name,count(distinct(d.hn) )as p1 ,count(distinct(d.vn)) as p2, '
+'count(distinct(v1.hn)) as a11,count(distinct(v1.vn)) as a12 ,'
+'count(distinct(v2.hn)) as a21,count(distinct(v2.vn)) as a22 ,'
+'count(distinct(v3.hn)) as a31,count(distinct(v3.vn)) as a32 ,'
+'count(distinct(v4.hn)) as a41,count(distinct(v4.vn)) as a42 ,'
+'count(distinct(v5.hn)) as a51,count(distinct(v5.vn)) as a52 ,'
+'count(distinct(v6.hn)) as a61,count(distinct(v6.vn)) as a62 ,'
+'count(distinct(v7.hn)) as a71,count(distinct(v7.vn)) as a72'
+'left outer join vn_stat v on v.vn=d.vn'
+'left outer join vn_stat v1 on v1.vn=d.vn and v1.hn in (select hn from vn_stat where vstdate between "'+d1+'" and "'+d2+'" and age_y between "0" and "2")'
+'left outer join vn_stat v2 on v2.vn=d.vn and v2.hn in (select hn from vn_stat where vstdate between "'+d1+'" and "'+d2+'" and age_y between "0" and "4")'
+'left outer join vn_stat v3 on v3.vn=d.vn and v3.hn in (select hn from vn_stat where vstdate between "'+d1+'" and "'+d2+'" and age_y between "3" and "5")'
+'left outer join vn_stat v4 on v4.vn=d.vn and v4.hn in (select hn from vn_stat where vstdate between "'+d1+'" and "'+d2+'" and age_y between "5" and "6")'
+'left outer join vn_stat v5 on v5.vn=d.vn and v5.hn in (select hn from vn_stat where vstdate between "'+d1+'" and "'+d2+'" and age_y between "6" and "12")'
+'left outer join vn_stat v6 on v6.vn=d.vn and v6.hn in (select hn from vn_stat where vstdate between "'+d1+'" and "'+d2+'" and age_y between "13" and "59")'
+'left outer join vn_stat v7 on v7.vn=d.vn and v7.hn in (select hn from vn_stat where vstdate between "'+d1+'" and "'+d2+'" and age_y between "60" and "200")'
+'left outer join hospcode h on h.hospcode=v.hospsub'
+'where d.vstdate between "'+d1+'" and "'+d2+'" '
+'group by v.hospsub order by v.hospsub desc ');

[woravet]

ลองแบบนี้มั๊ย
select a.hospsub,ifnull(h.name,'อื่นๆ') name
,count(distinct a.hn) 'case',count(a.hn) 'visit'

,concat(count(distinct if(a.age_y between 0 and 2,a.hn,null)),'/'
,count(if(a.age_y between 0 and 2,a.hn,null))) age_0_2

,concat(count(distinct if(a.age_y between 3 and 5,a.hn,null)),'/'
,count(if(a.age_y between 3 and 5,a.hn,null))) age_3_5

,concat(count(distinct if(a.age_y between 6 and 12,a.hn,null)),'/'
,count(if(a.age_y between 6 and 12,a.hn,null))) age_6_12

,concat(count(distinct if(a.age_y between 13 and 59,a.hn,null)),'/'
,count(if(a.age_y between 13 and 59,a.hn,null))) age_13_59

,concat(count(distinct if(a.age_y>=60,a.hn,null)),'/'
,count(if(a.age_y>=60,a.hn,null))) age_60_more

from (select d.vn,d.hn,v.age_y,v.aid
,if(v.hospsub='' or v.hospsub is null,(select hospitalcode from opdconfig)
,if(v.hospsub in (select hcode from pcu_child),v.hospsub,'99999')) hospsub
from dtmain d
left join vn_stat v on d.vn=v.vn
where d.vstdate between '2013-1-1' and '2013-1-31') a
left join hospcode h on h.hospcode=a.hospsub
group by a.hospsub

[prapansak]

ได้แล้ว ขอบคุณครับ