 SELECT a.villcode, COUNT( a.pid)'P60', COUNT( b.pid)'ScrCOPD',
FORMAT(COUNT( b.pid)*100/COUNT( a.pid),2)'%',
COUNT( c.pid)'Z720', COUNT( d.pid)'R688',
COUNT( e.pid)'R093', COUNT( f.pid)'R068'
FROM
(SELECT b.villcode, a.idcard, a.pid, a.birth, a.typelive
FROM person a
INNER JOIN village b ON b.villno=a.mumoi
WHERE a.typelive IN (1,3) AND a.dischargetype='9'
AND FORMAT(DATEDIFF(20140930,a.birth)/365.255,0) >=60) a
LEFT JOIN
(SELECT a.pid
FROM visit a
INNER JOIN visitdiag b ON b.visitno=a.visitno
WHERE a.visitdate BETWEEN '20141001' AND '20150930'
AND (b.diagcode='Z00.8' OR b.diagcode='Z13.8')
AND a.symptoms LIKE '%ปอด%'
GROUP BY 1) b ON b.pid=a.pid
LEFT JOIN
(SELECT a.pid
FROM visit a
INNER JOIN visitdiag b ON b.visitno=a.visitno
WHERE a.visitdate BETWEEN '20141001' AND '20150930'
AND b.diagcode='Z72.0'
AND a.symptoms LIKE '%ปอด%'
GROUP BY 1) c ON c.pid=b.pid
LEFT JOIN
(SELECT a.pid FROM visit a
INNER JOIN visitdiag b ON b.visitno=a.visitno
WHERE a.visitdate BETWEEN '20141001' AND '20150930'
AND b.diagcode='R68.8'
AND a.symptoms LIKE '%ปอด%'
GROUP BY 1) d ON d.pid=b.pid
LEFT JOIN
(SELECT a.pid
FROM visit a
INNER JOIN visitdiag b ON b.visitno=a.visitno
WHERE a.visitdate BETWEEN '20141001' AND '20150930'
AND b.diagcode='R09.3'
AND a.symptoms LIKE '%ปอด%'
GROUP BY 1) e ON e.pid=b.pid
LEFT JOIN
(SELECT a.pid
FROM visit a
INNER JOIN visitdiag b ON b.visitno=a.visitno
WHERE a.visitdate BETWEEN '20141001' AND '20150930'
AND b.diagcode='R06.8'
AND a.symptoms LIKE '%ปอด%'
GROUP BY 1) f ON f.pid=b.pid
GROUP BY 1;
