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

select cid,concat(p.pname,p.fname,’ ‘,p.lname)as name,o.hn,o.vstdate,o.waist,o.cc,o1.*
from ovstdiag o1
left join opdscreen o on o.hn=o1.hn and o.vstdate=o1.vstdate
left join person p on p.patient_hn=o.hn
where o1.icd10=’z000’ and (o.cc like ‘%ÊØ¢ÀÒ¾%’) and (o.vstdate between ‘2014-01-01’ and ‘2014-04-31′)

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

select cid,concat(p.pname,p.fname,’ ‘,p.lname)as name,o.hn,o.vstdate,o.waist,o.cc,o.*
from ovstdiag o1
left join opdscreen o on o.hn=o1.hn and o.vstdate=o1.vstdate
left join person p on p.patient_hn=o.hn
where o1.icd10=’z000’ and (o.vstdate between ‘2014-01-01’ and ‘2014-04-31’) and (o.cc like ‘%สุขภาพ%’)

select * from vn_stat
where ((pdx between ‘h25’ and ‘h269’) and ((dx0 between ‘z130’and ‘z139’) or (dx1 between ‘z130’and ‘z139’) or (dx2 between ‘z130’and ‘z139’) or (dx3 between ‘z130’and ‘z139’)or
(dx4 between ‘z130’and ‘z139’) or (dx5 between ‘z130’and ‘z139’))) or
((dx0 between ‘h25’ and ‘h269’) and ((pdx between ‘z130’and ‘z139’) or (dx1 between ‘z130’and ‘z139’) or (dx2 between ‘z130’and ‘z139’) or (dx3 between ‘z130’and ‘z139’)or
(dx4 between ‘z130’and ‘z139’) or (dx5 between ‘z130’and ‘z139’))) or
((dx1 between ‘h25’ and ‘h269’) and ((dx0 between ‘z130’and ‘z139’) or (pdx between ‘z130’and ‘z139’) or (dx2 between ‘z130’and ‘z139’) or (dx3 between ‘z130’and ‘z139’)or
(dx4 between ‘z130’and ‘z139’) or (dx5 between ‘z130’and ‘z139’))) or
((dx2 between ‘h25’ and ‘h269’) and ((dx0 between ‘z130’and ‘z139’) or (dx1 between ‘z130’and ‘z139’) or (pdx between ‘z130’and ‘z139’) or (dx3 between ‘z130’and ‘z139’)or
(dx4 between ‘z130’and ‘z139’) or (dx5 between ‘z130’and ‘z139’))) or
((dx3 between ‘h25’ and ‘h269’) and ((dx0 between ‘z130’and ‘z139’) or (dx1 between ‘z130’and ‘z139’) or (dx2 between ‘z130’and ‘z139’) or (pdx between ‘z130’and ‘z139’)or
(dx4 between ‘z130’and ‘z139’) or (dx5 between ‘z130’and ‘z139’))) or
((dx4 between ‘h25’ and ‘h269’) and ((dx0 between ‘z130’and ‘z139’) or (dx1 between ‘z130’and ‘z139’) or (dx2 between ‘z130’and ‘z139’) or (dx3 between ‘z130’and ‘z139’)or
(pdx between ‘z130’and ‘z139’) or (dx5 between ‘z130’and ‘z139’))) or
((dx5 between ‘h25’ and ‘h269’) and ((dx0 between ‘z130’and ‘z139’) or (dx1 between ‘z130’and ‘z139’) or (dx2 between ‘z130’and ‘z139’) or (dx3 between ‘z130’and ‘z139’)or
(dx4 between ‘z130’and ‘z139’) or (pdx between ‘z130’and ‘z139’)))

/*((dx0 between ‘h25’ and ‘h269’) and (pdx = ‘z139′ or dx1=’z139’ or dx2 = ‘z139’ or dx3=’z139’or dx4 = ‘z139′ or dx5=’z139’)) or
((dx1 between ‘h25’ and ‘h269’) and (pdx = ‘z139′ or dx0=’z139’ or dx2 = ‘z139’ or dx3=’z139’or dx4 = ‘z139′ or dx5=’z139’)) or
((dx2 between ‘h25’ and ‘h269’) and (pdx = ‘z139′ or dx0=’z139’ or dx1 = ‘z139’ or dx3=’z139’or dx4 = ‘z139′ or dx5=’z139’)) or
((dx3 between ‘h25’ and ‘h269’) and (pdx = ‘z139′ or dx0=’z139’ or dx2 = ‘z139’ or dx1=’z139’or dx4 = ‘z139′ or dx5=’z139’)) or
((dx4 between ‘h25’ and ‘h269’) and (pdx = ‘z139′ or dx0=’z139’ or dx2 = ‘z139’ or dx3=’z139’or dx1 = ‘z139′ or dx5=’z139’)) or
((dx5 between ‘h25’ and ‘h269’) and (pdx = ‘z139′ or dx0=’z139’ or dx2 = ‘z139’ or dx3=’z139’or dx4 = ‘z139′ or dx1=’z139’)) */

select p.hn,p.cid,concat(p.pname,p.fname,’ ‘,p.lname)as name,p.addrpart,p.moopart,t.full_name,o.app_user,o.app_cause,o.vstdate,o.nextdate
from oapp o
left join patient p on p.hn=o.hn
left join thaiaddress t on t.chwpart=p.chwpart and t.amppart=p.amppart and t.tmbpart=p.tmbpart
where o.clinic in (‘001′,’002′) and o.nextdate=’2014-05-08’

select p.patient_hn,p.cid,concat(p.pname,p.fname,’ ‘,p.lname)as name,p2.addrpart,p2.moopart,p2.tmbpart,p2.amppart,p2.chwpart,t.full_name,p.house_regist_type_id,d.*
from person_death d
left outer join person p on p.person_id=d.person_id
left join patient p2 on p2.hn=p.patient_hn
left join thaiaddress t on t.chwpart=p2.chwpart and t.amppart=p2.amppart and t.tmbpart=p2.tmbpart

where (d.death_date between ‘2000-10-01’ and ‘2014-04-31’) and (d.death_diag_1 = ”)
order by p2.chwpart,p2.amppart,p2.tmbpart,p2.moopart

select concat(p.pname,p.fname,’ ‘,p.lname)as name,p.death,d.death_date,p.cid,o.*
from ovstdiag o
left join person p on p.patient_hn=o.hn
left join death d on d.hn=p.patient_hn
where (o.icd10 between ‘J44’ and ‘j449′) and (p.death=’Y’)
group by o.hn

select sum(t.unitprice)as cost,concat(v.pdx,’-‘,v.dx0,’-‘,v.dx1,’-‘,v.dx2,’-‘,v.dx3,’-‘,v.dx4,’-‘,v.dx5)as OPD,
concat(a.pdx,’-‘,a.dx0,’-‘,a.dx1,’-‘,a.dx2,’-‘,a.dx3,’-‘,a.dx4,’-‘,a.dx5)as IPD,a.item_money,a.uc_money,a.regdate,k.department,s.name,o.*
from opdscreen o
left join opitemrece t on t.hn=o.hn and t.vstdate=o.vstdate
left join vn_stat v on v.hn=o.hn and v.vstdate=o.vstdate
left join an_stat a on a.hn=o.hn and a.vn=o.vn
left join kskdepartment k on k.depcode=o.screen_dep
left join spclty s on s.spclty=v.spclty
where o.vstdate between ‘2014-03-01’ and ‘2014-03-31’
group by o.hn,o.vstdate