select /*concat(p.addrpart,” ËÁÙè “,p.moopart,” µ.”,t3.name,” Í.”,t2.name,” ¨.”,t1.name,” “,p.po_code) as fulladdressname,*/v.prediag,v.hn,concat (p.pname,p.fname,’ ‘,p.lname)as nameA,
o.icd10,i.name as dx,k.name as ward,v.an
from iptdiag o
left outer join ipt v on v.an=o.an
left outer join ovst t on t.an=v.an
left outer join patient p on p.hn=v.hn
/*left outer join patient p on p.an=t.an */
/*left outer join thaiaddress t1 on t1.chwpart=p.chwpart and t1.amppart=”04″ and t1.tmbpart=”01″
left outer join thaiaddress t2 on t2.chwpart=p.chwpart and t2.amppart=p.amppart and t2.tmbpart=”01”
left outer join thaiaddress t3 on t3.chwpart=p.chwpart and t3.amppart=p.amppart and t3.tmbpart=p.tmbpart */
left outer join icd101 i on i.code=o.icd10

left outer join ward k on k.ward=v.ward

where (v.regdate between “2013-12-01” and “2013-12-31”) and (o.icd10 in (“F209″,”F29″,”F419″,”F410″,”F329″,”F76″,”Q409″,”F192″,”Q407″,”F510”))

select o.hn,v.cid,p.pname,p.fname,p.lname,p.birthday,concat(v.age_y,’.’,v.age_m)as age,v.vstdate,o.egfr,o.hba1c,v.pdx,v.dx0,v.dx1,v.dx2,v.dx3,v.dx4,v.dx5

from opdscreen o
left join vn_stat v on v.hn=o.hn
left join patient p on p.hn=v.hn
where o.hba1c is not null and o.hba1c < 7
group by o.hn

select v.village_moo,v.village_name,concat(p.pname,p.fname,’ ‘,p.lname) as name,if (p.sex=1,’ªÒÂ’,’Ë­Ô§’)as sex,p.cid,p.patient_hn,p.birthdate,p.club_list,p.age_y,p.age_m,p.age_d,p.death_date,pt.clinic
from person p
left outer join village v on v.village_id=p.village_id
left outer join religion r on r.religion = p.religion
left outer join patient pt on pt.cid=p.cid
where p.age_y > 59 and p.village_id not in (‘1’) and p.death_date is null
order by v.village_name

select o.hn,o.main_dep,o.vstdate,v.vstdate,v.vn,v.hn,v.pdx,v.dx0,v.dx1,v.dx2,v.dx3,v.dx4,v.dx5
from ovst o
left outer join vn_stat v on v.hn=o.hn and v.vstdate=o.vstdate
where (o.vstdate between ‘2014-09-01’ and ‘2014-09-31’)
and pdx like ‘%u%’

————————————————————————————————————————-

select o.hn,o.main_dep,o.vstdate,v.vstdate,v.vn,v.hn,v.pdx,v.dx0,v.dx1,v.dx2,v.dx3,v.dx4,v.dx5
from ovst o
left outer join vn_stat v on v.hn=o.hn and v.vstdate=o.vstdate
where (o.vstdate between ‘2013-12-01’ and ‘2014-01-31’)
and (
(v.pdx like ‘%u%’ and v.dx0 not like ‘%m%’ and v.dx1 not like ‘%m%’ and
v.dx2 not like ‘%m%’ and v.dx3 not like ‘%m%’ and v.dx4 not like ‘%m%’ and v.dx5 not like ‘%m%’) or
(v.dx0 like ‘%u%’ and v.pdx not like ‘%m%’ and v.dx1 not like ‘%m%’ and
v.dx2 not like ‘%m%’ and v.dx3 not like ‘%m%’ and v.dx4 not like ‘%m%’ and v.dx5 not like ‘%m%’) or
(v.dx1 like ‘%u%’ and v.pdx not like ‘%m%’ and v.dx0 not like ‘%m%’ and
v.dx2 not like ‘%m%’ and v.dx3 not like ‘%m%’ and v.dx4 not like ‘%m%’ and v.dx5 not like ‘%m%’) or
(v.dx2 like ‘%u%’ and v.pdx not like ‘%m%’ and v.dx0 not like ‘%m%’ and
v.dx1 not like ‘%m%’ and v.dx3 not like ‘%m%’ and v.dx4 not like ‘%m%’ and v.dx5 not like ‘%m%’) or
(v.dx3 like ‘%u%’ and v.pdx not like ‘%m%’ and v.dx0 not like ‘%m%’ and
v.dx2 not like ‘%m%’ and v.dx1 not like ‘%m%’ and v.dx4 not like ‘%m%’ and v.dx5 not like ‘%m%’) or
(v.dx4 like ‘%u%’ and v.pdx not like ‘%m%’ and v.dx0 not like ‘%m%’ and
v.dx2 not like ‘%m%’ and v.dx3 not like ‘%m%’ and v.dx1 not like ‘%m%’ and v.dx5 not like ‘%m%’) or
(v.dx5 like ‘%u%’ and v.pdx not like ‘%m%’ and v.dx0 not like ‘%m%’ and
v.dx2 not like ‘%m%’ and v.dx3 not like ‘%m%’ and v.dx4 not like ‘%m%’ and v.dx1 not like ‘%m%’))

select v.village_moo,v.village_name,
sum(case when p.sex=’1’then 1 else 0 end) as M,
sum(case when p.sex=’2′ then 1 else 0 end) as W,
sum(case when p.sex=’1′ or p.sex=’2′ then 1 else 0 end) as total
from person p
inner join village v on v.village_id=p.village_id
where (p.age_y between ’35’ and ‘200’) and (p.village_id not in (‘1′)) and (p.death !=””) and (p.house_regist_type_id=’1’)
group by p.village_id