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

Outlook (13)  เอกสารดาวน์โหลด

OPD

SELECT “10917” as hcode,a.vn,p.hn,a.cid,p.pname,p.fname,p.lname,p.birthday,
p.sex,a.vstdate,pty.name as pttypename,a.pdx,a.dx0,a.dx1,a.dx2,a.dx3,a.dx4,a.dx5,
a.op0,a.op1,a.op2,a.op3,a.op4,a.op5,
sum(if(c.income=’01’,c.rcptamt,0)) as inc01,
sum(if(c.income=’02’,c.rcptamt,0)) as inc02,
sum(if(c.income=’03’,c.rcptamt,0)) as inc03,
sum(if(c.income=’04’,c.rcptamt,0)) as inc04,
sum(if(c.income=’05’,c.rcptamt,0)) as inc05,
sum(if(c.income=’06’,c.rcptamt,0)) as inc06,
sum(if(c.income=’07’,c.rcptamt,0)) as inc07,
sum(if(c.income=’08’,c.rcptamt,0)) as inc08,
sum(if(c.income=’09’,c.rcptamt,0)) as inc09,
sum(if(c.income=’10’,c.rcptamt,0)) as inc10,
sum(if(c.income=’11’,c.rcptamt,0)) as inc11,
sum(if(c.income=’12’,c.rcptamt,0)) as inc12,
sum(if(c.income=’13’,c.rcptamt,0)) as inc13,
sum(if(c.income=’14’,c.rcptamt,0)) as inc14,
sum(if(c.income=’15’,c.rcptamt,0)) as inc15,
sum(if(c.income=’16’,c.rcptamt,0)) as inc16,
sum(if(c.income=’17’,c.rcptamt,0)) as inc17
from vn_stat a
left JOIN patient p on p.hn=a.hn
LEFT JOIN pttype pty on pty.pttype=a.pttype
left join incoth c on a.vn = c.vn
WHERE a.vstdate BETWEEN ‘2014-01-01’ and ‘2014-03-31′
group by a.vn

select 10917as hcode,a.an,a.hn,ov.vn,p.pname,p.fname,p.lname,p.birthday,p.sex,a.regdate,a.dchdate,w.name as wardname,
pty.name as pttypename,a.pdx,a.dx0,a.dx1,a.dx2,a.dx3,a.dx4,a.dx5,a.op0,a.op1,a.op2,a.op3,a.op4,a.op5,a.op6,
sum(if(c.income=’01’,c.rcptamt,0)) as inc01,
sum(if(c.income=’02’,c.rcptamt,0)) as inc02,
sum(if(c.income=’03’,c.rcptamt,0)) as inc03,
sum(if(c.income=’04’,c.rcptamt,0)) as inc04,
sum(if(c.income=’05’,c.rcptamt,0)) as inc05,
sum(if(c.income=’06’,c.rcptamt,0)) as inc06,
sum(if(c.income=’07’,c.rcptamt,0)) as inc07,
sum(if(c.income=’08’,c.rcptamt,0)) as inc08,
sum(if(c.income=’09’,c.rcptamt,0)) as inc09,
sum(if(c.income=’10’,c.rcptamt,0)) as inc10,
sum(if(c.income=’11’,c.rcptamt,0)) as inc11,
sum(if(c.income=’12’,c.rcptamt,0)) as inc12,
sum(if(c.income=’13’,c.rcptamt,0)) as inc13,
sum(if(c.income=’14’,c.rcptamt,0)) as inc14,
sum(if(c.income=’15’,c.rcptamt,0)) as inc15,
sum(if(c.income=’16’,c.rcptamt,0)) as inc16,
sum(if(c.income=’17’,c.rcptamt,0)) as inc17,
a.los,a.drg,i.rw,i.adjrw
from an_stat a
left JOIN patient p on p.hn=a.hn
LEFT JOIN pttype pty on pty.pttype=a.pttype
left join incith c on c.an = a.an
left outer join ward w on w.ward = a.ward
left outer join ipt i on i.an = a.an
left outer join ovst ov on ov.an=a.an
WHERE a.dchdate BETWEEN ‘2013-05-01’ and ‘2013-05-31’
group by a.an

select v.vstdate, v.hn, v.vn, concat(pt.pname,pt.fname,”  “,pt.lname) as ptname, v.pdx ,d.icode ,d.name,
d1.name
from vn_stat v
left outer join opitemrece o on o.vn = v.vn
left outer join patient pt on pt.hn = v.hn
left outer join doctor d1 on d1.code=v.dx_doctor
left outer join drugitems d on d.icode=o.icode
where v.vstdate between ‘2012-10-01’ and ‘2012-10-30’
and v.pdx not like ‘z%’ and    d.icode is null
group by v.vn
order by v.vstdate

select vn.pdx,vn.dx0,vn.dx1,vn.dx2,vn.dx3,vn.dx4,vn.dx5,c.* ,concat(p.pname,p.fname,’ ‘,p.lname) as patient_name ,n.name as clinic_name , p.cid,y.name as pttype_name ,
cs.name as clinic_subtype_name ,s.name as sex_name ,
d.name as doctor_name ,cm.clinic_member_status_name ,
((if(isnull(date_format(p.deathday,’%Y’)), date_format(now(),’%Y’), date_format(p.deathday,’%Y’)) – date_format(p.birthday,’%Y’)) –
(if(isnull(date_format(p.deathday,’00-%m-%d’)), date_format(now(),’00-%m-%d’), date_format(p.deathday,’00-%m-%d’))
< date_format(p.birthday,’00-%m-%d’))) as patient_age_y, u.name as staff_name,ov1.vstdate as last_cormobidity_screen_date ,
concat(hh.hosptype,’ ‘,hh.name) as dw_reg_hospital_name, concat(ph.hosptype,’ ‘,ph.name) as send_pcu_hospital_name
from clinicmember c
left join vn_stat vn on vn.hn=c.hn
left outer join patient p on p.hn = c.hn

left outer join clinic n on n.clinic = c.clinic
left outer join pttype y on y.pttype = p.pttype
left outer join clinic_subtype cs on cs.clinic_subtype_id = c.clinic_subtype_id
left outer join sex s on s.code = p.sex
left outer join doctor d on d.code = c.doctor
left outer join clinic_member_status cm on cm.clinic_member_status_id = c.clinic_member_status_id
left outer join opduser u on u.loginname = c.modify_staff
left outer join ovst ov1 on ov1.vn = c.last_cormobidity_screen_vn
left outer join hospcode hh on hh.hospcode = c.register_hospcode
left outer join hospcode ph on ph.hospcode = c.send_to_pcu_hcode
where c.clinic = ‘011’ and (c.discharge is null or c.discharge=’N’)
group by c.hn
order by c.pt_number,c.regdate

select distinct pt.hn,pt.cid,concat(pt.pname,pt.fname,’ ‘,pt.lname)as name,pt.addrpart ,pt.moopart,pt.tmbpart,pt.amppart,pt.chwpart
from patient pt,clinicmember c
where pt.hn = c.hn and c.clinic=’001′ and pt.chwpart=’32’ and pt.amppart=’04’ and pt.tmbpart=’01’
order by pt.moopart