SELECT 
concat(ctitle.titlename,person.fname," ",person.lname)as'ชื่อ-สกุลแม่'
,MAX(visitancmothercare.pregno) pregno,visitancmothercare.pid
,DATE_FORMAT(current_date,'%Y-%m-%d') - DATE_FORMAT(person.birth,'%Y-%m-%d') as age
,if(house.hno != '' ,CONVERT(concat(house.hno,' ม.',villno) USING utf8), CONVERT(concat('- ม.',villno) USING utf8) ) as address
,concat(pchild.fname,' ',pchild.lname) 'ชื่อสกุลลูก'
,count(distinct visitancmothercare.pid,visitancmothercare.pcucodeperson,visitancmothercare.pregno,visitancmothercare.datecare) as cdc
,case when ( visitancmothercare.datecare between MIN(visitancmothercare.datecare) and MAX(visitancmothercare.datecare)  and (count(distinct visitancmothercare.pid,visitancmothercare.pcucodeperson,visitancmothercare.pregno,visitancmothercare.datecare)  >=1 ) ) then DATE_FORMAT(MIN(visitancmothercare.datecare),'%Y-%m-%d') else null end 'เยี่ยมครั้งที่ 1'
,case when ( visitancmothercare.datecare between MIN(visitancmothercare.datecare) and MAX(visitancmothercare.datecare)  and (count(distinct visitancmothercare.pid,visitancmothercare.pcucodeperson,visitancmothercare.pregno,visitancmothercare.datecare)  >=2 ))  then DATE_FORMAT(visitancmothercare.datecare,'%Y-%m-%d')  else null end 'เยี่ยมครั้งที่ 2'
,case when ( visitancmothercare.datecare between MIN(visitancmothercare.datecare) and MAX(visitancmothercare.datecare)  and (count(distinct visitancmothercare.pid,visitancmothercare.pcucodeperson,visitancmothercare.pregno,visitancmothercare.datecare)  >=3 ))  then DATE_FORMAT(MAX(visitancmothercare.datecare),'%Y-%m-%d') else null end 'เยี่ยมครั้งที่3'
,curdate() as cdate
FROM  visitancmothercare
        left join visitancdeliverchild vchild on visitancmothercare.pid = vchild.pid and visitancmothercare.pcucodeperson = vchild.pcucodeperson
        left join visitancdeliver on visitancmothercare.pid  = visitancdeliver.pid  and visitancmothercare.pcucodeperson= visitancdeliver.pcucodeperson
        left join person   on visitancmothercare.pid = person.pid and visitancmothercare.pcucodeperson = person.pcucodeperson
        left join person pchild  on pchild.pid = vchild.pidchild and pchild.pcucodeperson = vchild.pcucodechild
        INNER JOIN ctitle on person.prename=ctitle.titlecode
        left join house on person.hcode = house.hcode and person.pcucodeperson = house.pcucode
        left join village on house.villcode = village.villcode and house.pcucode = village.pcucode
where visitancmothercare.datecare between '2014-10-01' and '2015-03-31'
GROUP BY  visitancmothercare.pcucode,visitancmothercare.pid,visitancmothercare.pcucodeperson,visitancmothercare.pregno;