select count(distinct a.vn) 'Total'
,count(distinct if(a.depcode='001',a.vn,null)) 'Room_Doctor1'
,count(distinct if(a.depcode='002',a.vn,null)) 'Room_Doctor2'
,count(distinct if(a.depcode='028',a.vn,null)) 'Room_Doctor3'
,count(distinct if(a.depcode='011',a.vn,null)) 'Room_ER'
,count(distinct if(a.depcode='027',a.vn,null)) 'Room_Screen'
,count(distinct if(a.depcode not in ('001','002','028','011','027'),a.vn,null)) 'Room_Other'
,count(distinct if(length(a.vn)>9,a.vn,null)) 'OPD'
,count(distinct if(length(a.vn)=9,a.vn,null)) 'IPD'
,sum(a.film_id=1) 'Film10X12'
,sum(a.film_id=2) 'Film14X14'
,sum(a.film_id=3) 'Film14X1'
,sum(a.film_id=1 and a.damage_id>0) 'Lost10X12'
,sum(a.film_id=2 and a.damage_id>0) 'Lost14X14'
,sum(a.film_id=3 and a.damage_id>0) 'Lost14X17'
,sum(a.damage_id=1) 'Damage1'
,sum(a.damage_id=2) 'Damage2'
,sum(a.damage_id=3) 'Damage3'
,sum(a.damage_id=4) 'Damage4'
,sum(a.damage_id=5) 'Damage5'
,sum(a.damage_id=6) 'Damage6'
,sum(a.damage_id=7) 'Damage7'
,sum(a.damage_id=8) 'Damage8'
,sum(a.damage_id=9) 'Damage9'
from (select x1.xray_order_number no,x1.vn,x1.hn
,if(length(x1.vn)=9,x1.vn,'') 'AN',ifnull(r1.xn,r2.xn) XN
,date(x1.order_date_time) date
,ifnull(rf1.film_id,rf2.film_id) film_id
,ifnull(rf1.damage_id,rf2.damage_id) damage_id
,k.depcode,k.department
from xray_head x1
left join xray_report r1 on x1.vn=r1.vn
left join xray_report r2 on x1.vn=r2.an
left join xray_report_film rf1 on r1.xn=rf1.xn
left join xray_report_film rf2 on r2.xn=rf2.xn
left join kskdepartment k on x1.department_list regexp k.department
where date(x1.order_date_time) between '2013-1-1' and '2013-1-31') a
แบบนี้ได้มั๊ย
หัวข้อ: ขอแบบฟอร์มรายงานสรุปงานเอ็กซเรย์ (อ่าน 6336 ครั้ง)