select p.pid,
concat(ctitle.titlename,p.fname,' ',p.lname)as pname,
p.birth as birth,
GetAgeYearNum(p.birth,'2014-10-01') as 'อายุ',
p.idcard as idcard,
p.hnomoi,
SUBSTRING(house.villcode,7,2) as moo,
visit.visitdate as วันที่รับบริการ ,cdrug.drugname,visitdrug.unit as จำนวนยาที่จ่าย
from
person p
inner join ctitle on p.prename = ctitle.titlecode
inner join house on p.pcucodeperson = house.pcucode and p.hcode = house.hcode
inner join visit on p.pid = visit.pid
inner join visitdrug on visit.visitno = visitdrug.visitno
inner join cdrug on visitdrug.drugcode = cdrug.drugcode
and CONCAT(p.pid,p.pcucodeperson) NOT IN (select CONCAT(persondeath.pid,persondeath.pcucodeperson)from persondeath)
and SUBSTRING(house.villcode,7,2) <> '00' and p.typelive in ('1','3') and visit.visitdate between '2014-10-01' and '2015-09-30'
and unit > '600'
order by visit.visitdate