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

select v.main_dep,s.name,k.department,o.*
from opdscreen o
inner join ovst v on v.hn=o.hn and v.vstdate=o.vstdate
inner join spclty s on s.spclty=v.spclty
inner join kskdepartment k on k.depcode=v.main_dep
where (o.vstdate between ‘2014-03-27’ and ‘2014-03-27’) and
(o.pulse is null or o.bpd is null or o.bps is null or o.bw is null or o.temperature is null or o.rr is null )

select p.patient_hn,w.person_id,count(w.person_id)
from person_wbc w
inner join person p on p.person_id=w.person_id
group by w.person_id

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

select p.patient_hn,concat(p.pname,p.fname,’ ‘,p.lname)as name,w.person_id,p.age_y,p.age_m,p.age_d,count(w.person_id)as ‘¨Ó¹Ç¹·Õè«éÓ’
from person_wbc w
inner join person p on p.person_id=w.person_id
group by w.person_id
having count(w.person_id)>1

select vn.vstdate, concat(p1.pname,” “,p1.fname,” “,p1.lname)as ptname, p1.cid, vn.age_y,
d1.feel_depression_2_week as 2Q_1, d1.feel_boring_2_week as 2Q_2,
d1.no_depression as “T2Q”,
d1.depression_score as “T 9Q”, d1.suicide_score as “T 8Q”,vn.pdx,vn.dx0,vn.dx1,vn.dx2,vn.dx3,vn.dx4,vn.dx5,pt.addrpart,vn.moopart,t1.full_name,
pt.death,pt.type_area
from depression_screen d1
left outer join depression_screen_evaluate d2 on d2.depression_screen_evaluate_id = d1.depression_screen_evaluate_id
left outer join suicide_screen_evaluate d3 on d3.suicide_screen_evaluate_id = d1.suicide_screen_evaluate_id
left outer join vn_stat vn on vn.vn = d1.vn
left outer join person p1 on p1.patient_hn=vn.hn
left outer join house h1 on h1.house_id=p1.house_id
left outer join village v1 on v1.village_id=h1.village_id
left outer join thaiaddress t1 on t1.addressid=v1.address_id
left outer join patient pt on pt.hn=p1.patient_hn
where (vn.vstdate between’2012-11-01′ and ‘2014-04-31’ ) /*and (vn.pdx between ‘f32’ and ‘f329’) or (vn.pdx between ‘f33’ and ‘f339’) or (vn.pdx between ‘f34’ and ‘f349’) or (vn.pdx between ‘f38’ and ‘f389’) or (vn.pdx between ‘f39’ and ‘f399’)
or (vn.dx0 between ‘f32’ and ‘f329’) or (vn.dx0 between ‘f33’ and ‘f339’) or (vn.dx0 between ‘f34’ and ‘f349’) or (vn.dx0 between ‘f38’ and ‘f389’) or (vn.dx0 between ‘f39’ and ‘f399’)
or (vn.dx1 between ‘f32’ and ‘f329’) or (vn.dx1 between ‘f33’ and ‘f339’) or (vn.dx1 between ‘f34’ and ‘f349’) or (vn.dx1 between ‘f38’ and ‘f389’) or (vn.dx1 between ‘f39’ and ‘f399’)
or (vn.dx2 between ‘f32’ and ‘f329’) or (vn.dx2 between ‘f33’ and ‘f339’) or (vn.dx2 between ‘f34’ and ‘f349’) or (vn.dx2 between ‘f38’ and ‘f389’) or (vn.dx2 between ‘f39’ and ‘f399’)
or (vn.dx3 between ‘f32’ and ‘f329’) or (vn.dx3 between ‘f33’ and ‘f339’) or (vn.dx3 between ‘f34’ and ‘f349’) or (vn.dx3 between ‘f38’ and ‘f389’) or (vn.dx3 between ‘f39’ and ‘f399’)
or (vn.dx4 between ‘f32’ and ‘f329’) or (vn.dx4 between ‘f33’ and ‘f339’) or (vn.dx4 between ‘f34’ and ‘f349’) or (vn.dx4 between ‘f38’ and ‘f389’) or (vn.dx4 between ‘f39’ and ‘f399’)
or (vn.dx5 between ‘f32’ and ‘f329’) or (vn.dx5 between ‘f33’ and ‘f339’) or (vn.dx5 between ‘f34’ and ‘f349’) or (vn.dx5 between ‘f38’ and ‘f389’) or (vn.dx5 between ‘f39’ and ‘f399′)
*/group by p1.cid
order by d1.screen_datetime

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

SELECT DATE_FORMAT(vn.vstdate,’%Y-%m-%d’) as vstdate,p.pname,p.fname,p.lname,p.cid,vn.age_y,p.birthday,
if((ds.no_depression=”Y”),”2″,”1″) as resultscreen,
(score_9q_1+score_9q_2+score_9q_3+score_9q_4+score_9q_5+score_9q_6+score_9q_7+score_9q_8+score_9q_9) AS score_9q,
(score_8q_1+score_8q_2+score_8q_3+score_8q_4+score_8q_5+score_8q_6+score_8q_7+score_8q_8) As score_8q,
if(c.clinic != “”,1,if(opd.pregnancy=”Y”,2,if(vn.age_y>=60,4,8))) as riskgroup,
if(c.clinic = “003”,1,0) as depression,od.icd10,
p.chwpart as informaddr
FROM depression_screen ds
INNER JOIN vn_stat vn ON vn.vn=ds.vn
INNER JOIN opdscreen opd on opd.vn=vn.vn
INNER JOIN patient p ON p.hn=vn.hn
INNER JOIN depression_screen_evaluate dse ON dse.depression_screen_evaluate_id=ds.depression_screen_evaluate_id
INNER JOIN ovstdiag od ON od.vn=vn.vn
LEFT OUTER JOIN clinicmember c ON p.hn=c.hn
LEFT OUTER JOIN referout r ON vn.vn=r.vn
WHERE vn.vstdate BETWEEN “2004-01-01” AND “2014-02-28” and od.icd10 between “F32” and “F39”
group by vn.vn
ORDER BY vn.vstdate

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

select ds.screen_datetime,ds.feel_depression_2_week,ds.feel_boring_2_week,ps.age_y,o.*
from ovstdiag o
left outer join patient p on p.hn=o.hn
left outer join person ps on ps.patient_hn=p.hn
left outer join depression_screen ds on ds.vn=o.vn
where (o.icd10 between “F32” and “F39” ) AND (o.vstdate BETWEEN “2004-01-01” AND “2014-04-01”) and (ds.screen_datetime is not null)
group by o.vn

select concat(p.pname,p.fname,’ ‘,p.lname) as ptname,p.patient_hn ,p.age_y,p.age_m,vl.student_vaccine_id,s.student_vaccine_name,vs.school_name,vsc.village_school_class_name
from village_student v
left outer join person p on p.person_id=v.person_id
left outer join village_student_vaccine vsv on vsv.village_student_id=v.village_student_id
left outer join village_student_vaccine_list vl on vl.village_student_vaccine_id=vsv.village_student_vaccine_id
left outer join student_vaccine s on s.student_vaccine_id=vl.student_vaccine_id
left outer join village_school vs on vs.village_school_id=v.village_school_id
left outer join village_school_class vsc on vsc.village_school_class_id=v.village_school_class_id
where v.village_school_class_id=’9’

select distinct o.vstdate,o.vn,o.hn,concat(p.pname,p.fname,” “,p.lname)as name,if(p.sex=”1”,’M’,’W’)as sex,v.age_y,o.bmi,e.r01,e.r02,e.r03,e.rph,e.l01,e.l02,e.l03,e.lph
from opdscreen o
left outer join patient p on p.hn=o.hn
left outer join eye_screen e on e.vn=o.vn
left outer join vn_stat v on e.vn=v.vn
where o.vstdate between ‘2014-01-01’ and ‘2014-02-15′ and v.age_y >= ’60’

select concat(p.pname,p.fname,’ ‘,p.lname)as name,p.addrpart,p.moopart,p.tmbpart,p.amppart,p.chwpart,t.name as amper,t.full_name,a.*
from opd_allergy a
left outer join patient p on p.hn=a.hn
left outer join thaiaddress t on t.tmbpart=p.tmbpart and t.amppart=p.amppart and t.chwpart=p.chwpart

var

name,cname,ds1,ds2 : string;
d1,d2:DateTime;

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

procedure GlobalOnCreate;

begin

name := GetPickupList(‘select name from drugitems where units=”àÁç´” ‘);
cname := GetSQLStringData(‘select icode from drugitems where name = “‘+name+'” ‘);

GetDateRangeDialog(d1,d2);
ds1:=formatdatetime(‘yyyy-mm-dd’,d1);
ds2:=formatdatetime(‘yyyy-mm-dd’,d2);
ChangeReportSQL(‘select o.*,d.* ‘+
‘ from opitemrece o ‘+
‘ left outer join drugitems d on d.icode=o.icode ‘+
‘ where o.icode =”‘+cname+'” ‘+
‘ and o.vstdate between “‘+ds1+'” and “‘+ds2+'” ‘+
‘ order by o.vstdate ‘
);

end;