 SET @bdate = '2014-04-01', @edate = '2015-03-31';
SET @bbdate = '2011-10-01', @ebdate = '2014-09-30';

SELECT
person.pcucodeperson,person.pid,CONCAT(ctitle.titlename,person.fname,' ',lname) AS 'ชื่อ - นามสกุล',person.birth,CONCAT(getAgeYearNum(person.birth,@edate),
' - ',getAgeMonthNum(person.birth,@edate),' - ',getAgeDayNum(person.birth,@edate)) AS 'อายุ (ป - ด - ว)',house.hno AS 'บ้านเลขที่',RIGHT(house.villcode,2) AS 'หมู่ที่',
vDent.MaxDate AS 'วันที่รับบริการ',vDent.drugcode AS 'รหัสหัตถการ'
FROM
person
INNER JOIN chospital ON person.pcucodeperson = chospital.hoscode
INNER JOIN ctitle ON person.prename = ctitle.titlecode
INNER JOIN house ON person.pcucodeperson = house.pcucodeperson AND person.hcode = house.hcode
LEFT OUTER JOIN persondeath ON persondeath.pcucodeperson = person.pcucodeperson AND persondeath.pid = person.pid
LEFT OUTER JOIN
(
SELECT visit.pcucode,MAX(visit.visitdate) AS MaxDate,visit.pid,visitdrug.drugcode
FROM visit
INNER JOIN visitdrug ON visitdrug.pcucode = visit.pcucode AND visitdrug.visitno = visit.visitno
WHERE visit.visitdate BETWEEN @bdate AND @edate AND visitdrug.drugcode = '2330011'
GROUP BY visit.pcucode,visit.pid
) AS vDent ON person.pcucodeperson = vDent.pcucode AND person.pid = vDent.pid
WHERE person.birth BETWEEN @bbdate AND @ebdate AND person.nation = '99' AND (persondeath.deaddate > @bdate OR ISNULL(persondeath.deaddate))
AND person.typelive IN ('0','1','3')
ORDER BY RIGHT(house.villcode,2),house.hno;

