โรงพยาบาลจอมพระ

select concat(p.pname,p.fname,’ ‘,p.lname)as name,t.pcode,o.vn,o.icd10,o.hn,o.vstdate,o.vsttime,o.diagtype
from clinicmember c
left join ovstdiag o on o.hn=c.hn
left join person p on p.patient_hn=c.hn
left join pttype t on t.pttype=p.pttype

where (o.icd10 between ‘e110’ and ‘e118′) and (t.pcode=’uc’) and (o.vstdate between ‘2013-10-01’ and ‘2014-05-31’)

group by o.hn
order by o.vstdate

select o.icode,d.did,d.name,o.qty,o.unitprice,o.hn,o.an,d.unitcost,d.ipd_price,o.vstdate
from opitemrece o
left join drugitems d on d.icode =o.icode
where o.hn=’000046778′ and o.an =’570001149′ and (vstdate between ‘2014-05-11’ and ‘2014-05-14’) and (d.unitcost<>”)
order by o.vstdate

select concat(p.pname,p.fname,’ ‘,p.lname)as name,p2.age_y,s.bps,s.bpd,o.icd10,c.*
from clinicmember c
left join ovstdiag o on o.hn=c.hn
left join opdscreen s on s.hn=c.hn
left join patient p on p.hn=c.hn
left join person p2 on p2.patient_hn=c.hn
where c.clinic =’001’ and (o.icd10 between ‘e10’ and ‘e149’) and (s.bps<=130 and s.bpd<=80)
group by c.hn,o.icd10

select concat(p.pname,p.fname,’ ‘,p.lname)as name,p2.age_y,s.bps,s.bpd,o.icd10,c.*
from clinicmember c
left join ovstdiag o on o.hn=c.hn
left join opdscreen s on s.hn=c.hn
left join patient p on p.hn=c.hn
left join person p2 on p2.patient_hn=c.hn
where c.clinic =’002’ and (o.icd10 between ‘i10’ and ‘i159’) and (s.bps<=140 and s.bpd<=90)
group by c.hn,o.icd10

select concat(p.pname,p.fname,’ ‘,p.lname)as name,p.cid,a.age_y,a.hn,a.an,a.regdate,a.dchdate,a.pdx
from an_stat a
left join person p on p.patient_hn=a.hn
where (a.dchdate between ‘2013-10-01’and ‘2014-06-31’)and ((a.pdx between ‘j44’ and ‘j449′) or a.pdx=’j46’)
order by p.cid,a.dchdate

select o.smoking_type_id,concat(p.pname,p.fname,’ ‘,p.lname)as name,p.cid,p.patient_hn,concat(p.age_y,’ »Õ’)as age,concat(pt.addrpart,’ ËÁÙè ‘,pt.moopart,’ ‘,t.full_name)as address,p.house_regist_type_id,sm.smoking_type_name
from opdscreen o
left join person p on p.patient_hn=o.hn
left join smoking_type sm on sm.smoking_type_id=o.smoking_type_id
left join patient pt on pt.hn=o.hn
left join thaiaddress t on t.chwpart=pt.chwpart and t.amppart=pt.amppart and t.tmbpart=pt.tmbpart

where (o.vstdate between ‘2014-01-01’ and ‘2014-01-31’) and
(o.smoking_type_id in (‘2′,’3′) and
(p.age_y between ’15’ and ’19’)) and
(p.house_regist_type_id in (‘1′,’3′))

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

select o.drinking_type_id,concat(p.pname,p.fname,’ ‘,p.lname)as name,p.cid,p.patient_hn,concat(p.age_y,’ »Õ’)as age,
concat(pt.addrpart,’ ËÁÙè ‘,pt.moopart,’ ‘,t.full_name)as address,p.house_regist_type_id,sm.drinking_type_name
from opdscreen o
left join person p on p.patient_hn=o.hn
left join drinking_type sm on sm.drinking_type_id=o.drinking_type_id
left join patient pt on pt.hn=o.hn
left join thaiaddress t on t.chwpart=pt.chwpart and t.amppart=pt.amppart and t.tmbpart=pt.tmbpart

where (o.vstdate between ‘2014-01-01’ and ‘2014-01-31’) and
(o.drinking_type_id in (‘2′,’3′) and
(p.age_y between ’15’ and ’19’)) and
(p.house_regist_type_id in (‘1′,’3’))

select concat(p.pname,p.fname,’ ‘,p.lname)as name,o.hn,p2.age_y,concat(p.addrpart,’ ËÁÙè ‘,p.moopart,t.full_name ) as address,s.bps,s.bpd,o.icd10,diagtype
from ovstdiag o
left join patient p on p.hn=o.hn
left join person p2 on p2.patient_hn=o.hn
left join opdscreen s on s.vstdate=o.vstdate and s.hn=o.hn
left join thaiaddress t on t.chwpart=p.chwpart and t.amppart=p.amppart and t.tmbpart=p.tmbpart

where (o.vstdate between ‘2014-01-01’ and ‘2014-01-31’) and (o.icd10 like ‘%i10%’ or o.icd10 like ‘%i152%’) and (s.bps <140 and s.bpd<90)

select concat(p.pname,p.fname,’ ‘,p.lname)as name,o.hn,o.vstdate,o.vsttime,concat(p.age_y,’ »Õ ‘ ,p.age_m,’ à´×͹’)as age,if(p.sex=’1′,’ªÒÂ’,’Ë­Ô§’)as sex,d.icd10,i.name as Fullname,i.tname
from ovst o
left join person p on p.patient_hn=o.hn
left join ovstdiag d on d.hn=o.hn and d.vstdate=o.vstdate and d.diagtype=’1′
left join icd101 i on i.code=d.icd10

where o.vstdate in(‘2014-04-24′,’2014-05-26′) and o.spclty in (’13’)
order by o.vstdate,o.vsttime

select vn.hn,vn.vn,vn.cid,concat(p.pname,p.fname,’ ‘,p.lname) as ptname,vn.vstdate,vn.pdx,vn.dx0,vn.dx1,vn.dx2,vn.dx3,vn.dx4,op.icode,d.name as drug,op.qty,(d.unitprice*op.qty) as income,d.antibiotic
from opitemrece op
join vn_stat vn on vn.vn = op.vn
join patient p on p.hn=vn.hn
join drugitems d on d.icode=op.icode
where op.vstdate between ‘2012-11-01’ AND ‘2013-09-30’
group by vn.vn

select c.hn,concat(p.pname,p.fname,’ ‘,p.lname)as name,v.village_name,concat(pt.addrpart,’ ËÁÙè·Õè ‘,pt.moopart,’ ‘,t.full_name)as address,p.cid,
p.age_y,p.house_regist_type_id,c.clinic,n.name,c.regdate
from clinicmember c
left join person p on p.patient_hn=c.hn
left join patient pt on pt.hn=c.hn
left join village v on v.village_id=p.village_id
left join thaiaddress t on t.chwpart=pt.chwpart and t.amppart=pt.amppart and t.tmbpart=pt.tmbpart
left join clinic n on n.clinic=c.clinic
where c.regdate > ‘2013-10-01’ and (p.village_id not in (‘1’))
order by v.village_id