set @ds = "2012-10-01";
set @de = "2013-09-30";
select
person.pcucodeperson AS 'สถานบริการ',
count(DISTINCT case when personchronic.chroniccode between 'E10' and 'E15' and person.typelive in (1,3) then person.pid else null end) as DM,
count(DISTINCT CASE WHEN visitlabchcyhembmsse.labcode = 'MS01020201' and person.typelive in (1,3) then person.pid else null end) as 'ตรวจMicro alb',
count(DISTINCT case when ncdservice.examfoot in ('0','1','2','3','4','5') and person.typelive in (1,3) then person.pid else null end) as 'ตรวจเท้า',
count(DISTINCT case when ncdservice.eyeopthalmoscope in (2,4) and person.typelive in (1,3)then person.pid ELSE null end) as 'fundus camera',
count(DISTINCT CASE WHEN visitlabchcyhembmsse.labcode = 'MS01020201' and ncdservice.examfoot in ('0','1','2','3','4','5') and ncdservice.eyeopthalmoscope in (2,4)
and person.typelive in (1,3) then person.pid else null end) as 'ตรวจMicro alb+เท้า+ตา'
from person
inner join personchronic on person.pid=personchronic.pid
inner join cdisease on personchronic.chroniccode=cdisease.diseasecode
inner join house on person.hcode = house.hcode and person.pcucodeperson = house.pcucode
inner join visit ON person.pcucodeperson = visit.pcucodeperson
and person.pid = visit.pid
left outer JOIN ncdservice ON personchronic.pid = ncdservice.pid and ncdservice.date_serv between @ds and @de
left outer join visitlabchcyhembmsse ON visit.pcucode = visitlabchcyhembmsse.pcucode
and visit.visitno = visitlabchcyhembmsse.visitno
inner join village on house.villcode = village.villcode and house.pcucode = village.pcucode
WHERE right(village.villcode,2)!= '00'and person.typelive in (1,3) and nation = 99 and origin = 99 and person.dischargetype != '1'
AND concat(person.pid,person.pcucodeperson)NOT IN (select concat(persondeath.pid,persondeath.pcucodeperson)
from persondeath where deaddate <= CURDATE() or deaddate is null)
and personchronic.chroniccode between 'E10' and 'E15'
GROUP BY person.pcucodeperson;