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

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