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

Script ดึง IPD Data

SELECT “11056” as hcode,a.an,p.hn,a.vn,p.pname,p.fname,p.lname,p.birthday,p.sex,a.regdate,a.dchdate,w.name as wardname,s.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,a.inc16 as inc01,a.inc08 as inc02 ,iis.uinc_drug as inc03,0 as inc04,iis.uinc_nondrug as inc05,a.inc02 as inc06,a.inc01 as inc07,a.inc04 as inc08,a.inc05 as inc09,a.inc09 as inc10,a.inc06 as inc11,a.inc14 as inc12,a.inc11 as inc13,a.inc13 as inc14,0 as inc15,a.inc17 as inc16,0 as inc17,iis.uinc_drug as incDrug,a.los,a.drg,a.rw,i.adjrw from an_stat a
left JOIN patient p on p.hn=a.hn
left join inc_ipd_stat iis on iis.an=a.an
left JOIN ward w on w.ward=a.ward
LEFT JOIN pttype pty on pty.pttype=a.pttype
left JOIN pcode s on s.code=pty.pcode
LEFT JOIN ipt i ON i.an=a.an
WHERE a.dchdate BETWEEN ‘2012-11-01’ and ‘2013-03-31’

Script ดึง OPD Data

SELECT “11056” as hcode,a.vn,a.vstdate,p.hn,a.cid,p.pname,p.fname,p.lname,p.birthday,p.sex,s.name as pttypename,”OPD” as WardName,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.inc16 as inc01,a.inc08 as inc02 ,inc_drug as inc03,0 as inc04,inc_nondrug as inc05,a.inc02 as inc06,a.inc01 as inc07,a.inc04 as inc08,a.inc05 as inc09,a.inc09 as inc10,a.inc06 as inc11,a.inc14 as inc12,a.inc11 as inc13,a.inc13 as inc14,0 as inc15,a.inc17 as inc16,0 as inc17,a.inc_drug as incdrug
from vn_stat a
left JOIN patient p on p.hn=a.hn
LEFT JOIN pttype pty on pty.pttype=a.pttype
left JOIN pcode s on s.code=pty.pcode
WHERE a.vstdate BETWEEN ‘2012-11-01’ and ‘2013-03-31’

เครดิตคุณ Terminator2013 บอร์ด HOSxP

select a.full_name as card,t.informaddr,t.type_area,p.*
from person p
left join patient t on t.cid=p.cid
left join thaiaddress a on a.chwpart= t.chwpart and a.amppart=t.amppart and a.tmbpart=t.tmbpart
where p.house_regist_type_id in (‘3’) and p.village_id in (‘1’)

select t.moopart,t.tmbpart,t.amppart,t.chwpart,p.house_regist_type_id,p.*
from person p
left outer join patient t on t.cid=p.cid
where p.house_regist_type_id=’1′ and (t.chwpart not in (’32’) or t.amppart not in (’04’))

select distinct o.vsttime,o.rxdate,v.vstdate,v.hn,v.cid,concat(p.pname,p.fname,’ ‘,p.lname)as nameA,
v.age_y,v.pttype,t.name,v.pcode,s.spclty,s.name,v.pdx,concat(v.dx0,’, ‘,v.dx1,’, ‘,v.dx2,’, ‘,v.dx3,’, ‘,v.dx4,’, ‘,v.dx5)as sec,v.income,v.paid_money,
v.rcpt_money,v.discount_money
from vn_stat v
left outer join patient p on p.hn=v.hn
left outer join pttype t on t.pttype=v.pttype
left outer join spclty s on s.spclty=v.spclty
left outer join rcpt_debt r on r.hn = v.hn
left outer join opitemrece o on v.vn=o.vn
where (v.vstdate between ‘2013-11-11’ and ‘2013-11-11′)
and (v.pdx =’u77’ or v.dx0 = ‘u77’ or v.dx1 = ‘u77’ or v.dx2 = ‘u77’ or v.dx3 = ‘u77’ or v.dx4 = ‘u77’ or v.dx5 = ‘u77′)
and (v.pttype not in (’02’,’03’,’04’,’05’,’08’,’09’,’11’,’12’,’13’,’15’,’20’,’36’,’38’,’75’,’98’,’91’,’86’,’88’,’89’,’87’,’85’))
group by v.vn

union
select distinct o.vsttime,o.rxdate,v.vstdate,v.hn,v.cid,concat(p.pname,p.fname,’ ‘,p.lname)as nameA,
v.age_y,v.pttype,t.name,v.pcode,s.spclty,s.name,v.pdx,concat(v.dx0,’, ‘,v.dx1,’, ‘,v.dx2,’, ‘,v.dx3,’, ‘,v.dx4,’, ‘,v.dx5)as sec,v.income,v.paid_money,
v.rcpt_money,v.discount_money
from vn_stat v
left outer join patient p on p.hn=v.hn
left outer join pttype t on t.pttype=v.pttype
left outer join spclty s on s.spclty=v.spclty
left outer join rcpt_debt r on r.hn = v.hn
left outer join opitemrece o on v.vn=o.vn
where (v.vstdate between ‘2013-11-11’ and ‘2013-11-11’)
and ((v.pdx between ‘k00’and ‘k14′) or (v.pdx=’z012’) or (v.dx0 between ‘k00’and ‘k14′) or (v.dx0=’z012’) or (v.dx1 between ‘k00’and ‘k14′) or (v.dx1=’z012’) or (v.dx2 between ‘k00’and ‘k14′) or (v.dx2=’z012’)
or (v.dx3 between ‘k00’and ‘k14′) or (v.dx3=’z012’) or (v.dx4 between ‘k00’and ‘k14′) or (v.dx4=’z012’) or (v.dx5 between ‘k00’and ‘k14′) or (v.dx5=’z012′))
and (v.pttype not in (’02’,’03’,’04’,’05’,’08’,’09’,’11’,’12’,’13’,’15’,’20’,’36’,’38’,’75’,’98’,’91’,’86’,’88’,’89’,’87’,’85’))
and (v.paid_money > 0 or v.rcpt_money >0)
group by v.vn

union
select distinct o.vsttime,o.rxdate,v.vstdate,v.hn,v.cid,concat(p.pname,p.fname,’ ‘,p.lname)as nameA,
v.age_y,v.pttype,t.name,v.pcode,s.spclty,s.name,v.pdx,concat(v.dx0,’ -‘,v.dx1,’ -‘,v.dx2,’ -‘,v.dx3,’ -‘,v.dx4,’ -‘,v.dx5)as sec,v.income,v.paid_money,
v.rcpt_money,v.discount_money
from vn_stat v
left outer join patient p on p.hn=v.hn
left outer join pttype t on t.pttype=v.pttype
left outer join spclty s on s.spclty=v.spclty
left outer join rcpt_debt r on r.hn = v.hn
left outer join opitemrece o on v.vn=o.vn
where (v.vstdate between ‘2013-11-11’ and ‘2013-11-11’)
and ((v.pdx between ‘z021’and ‘z028’) or (v.dx0 between ‘z021’and ‘z028’) or (v.dx1 between ‘z021’and ‘z028’) or (v.dx2 between ‘z021’and ‘z028’)
or (v.dx3 between ‘z021’and ‘z028’) or (v.dx4 between ‘z021’and ‘z028’) or (v.dx5 between ‘z021’and ‘z028′) )
group by v.vn

union
select distinct o.vsttime,o.rxdate,v.vstdate,v.hn,v.cid,concat(p.pname,p.fname,’ ‘,p.lname)as nameA,
v.age_y,v.pttype,t.name,v.pcode,s.spclty,s.name,v.pdx,concat(v.dx0,’ -‘,v.dx1,’ -‘,v.dx2,’ -‘,v.dx3,’ -‘,v.dx4,’ -‘,v.dx5)as sec,v.income,v.paid_money,
v.rcpt_money,v.discount_money
from vn_stat v
left outer join patient p on p.hn=v.hn
left outer join pttype t on t.pttype=v.pttype
left outer join spclty s on s.spclty=v.spclty
left outer join rcpt_debt r on r.hn = v.hn
left outer join opitemrece o on v.vn=o.vn
where (v.vstdate between ‘2013-11-11’ and ‘2013-11-11′) and (v.paid_money > 0 or v.rcpt_money >0)
group by v.vn

union
select distinct o.vsttime,o.rxdate,v.vstdate,v.hn,v.cid,concat(p.pname,p.fname,’ ‘,p.lname)as nameA,
v.age_y,v.pttype,t.name,v.pcode,s.spclty,s.name,v.pdx,concat(v.dx0,’, ‘,v.dx1,’, ‘,v.dx2,’, ‘,v.dx3,’, ‘,v.dx4,’, ‘,v.dx5)as sec,v.income,v.paid_money,
v.rcpt_money,v.discount_money
from vn_stat v
left outer join patient p on p.hn=v.hn
left outer join pttype t on t.pttype=v.pttype
left outer join spclty s on s.spclty=v.spclty
left outer join rcpt_debt r on r.hn = v.hn
left outer join opitemrece o on v.vn=o.vn
where (v.vstdate between ‘2013-11-11’ and ‘2013-11-11′)
and (v.pttype =(’84’))
group by v.vn

ขอ sql ที่เอาข้อมูล ส่วนสูง(height) ในตาราง Patient มาใส่ใน ส่วนสูง(height) ในตาราง opdscreen
–  patient.height ไม่เท่ากับ 0
– opdscreen.height = 0 หรือ ค่าว่าง
– update ข้อมูลในปี 2552(1 มค.52-31 ธ.ค.52)

update opdscreen o

inner join paitent p on p.hn=o.hn

set  o.height=p.height  where (o.height=0 or o.height is null ) and (p.height>0)  and (vstdate between “2009-01-01” and “2009-12-31”)

ที่มาจาก hosxp.net