SELECT s.* , i.name AS icd_name,concat(p.pname,p.fname,' ',p.lname) AS ptname ,
concat(p.mathername,'/', p.fathername) AS parent_name,
concat(s.addr,' ËÁÙè ',s.moo,' µ .',t3.name ,' Í.',t2.name,' ¨.',t1.name) AS address_name1,
oc.name AS occupation_name,
sx.name AS sex_name ,
m.name AS marrystatus_name ,
v.age_y ,
n.name AS nationality_name,
t.name AS stat_name
FROM surveil_member s
LEFT OUTER JOIN report506status t ON t.code=s.ptstat
LEFT OUTER JOIN opdscreen o ON o.vn=s.vn
LEFT OUTER JOIN patient p ON p.hn=s.hn
LEFT OUTER JOIN occupation oc ON oc.occupation = p.occupation
LEFT OUTER JOIN sex sx ON sx.code=p.sex
LEFT OUTER JOIN marrystatus m ON m.code=p.marrystatus
LEFT OUTER JOIN nationality n ON n.nationality = p.nationality
LEFT OUTER JOIN icd101 i ON i.code=SUBSTRING(s.pdx,1,3)
LEFT OUTER JOIN thaiaddress t1 ON t1.chwpart = s.chwpart AND t1.codetype='1'
LEFT OUTER JOIN thaiaddress t2 ON t2.chwpart = s.chwpart AND t2.amppart=s.amppart AND t2.codetype='2'
LEFT OUTER JOIN thaiaddress t3 ON t3.chwpart = s.chwpart AND t3.amppart=s.amppart
AND t3.tmbpart=s.tmbpart AND t3.codetype='3'
LEFT OUTER JOIN vn_stat v ON v.vn=s.vn
WHERE s.report_date BETWEEN "2008-03-01"AND "2008-03-31"<-------แก้เป็นช่วงวันที่แทน
ORDER BY sv_number