BMS-HOSxP Community
HOSxP => แจ้งปัญหา / ขอความช่วยเหลือ => ข้อความที่เริ่มโดย: chula ที่ กรกฎาคม 22, 2011, 14:01:16 PM
-
ขอควมช่วยเหลือจ๊ะ
ต้องหาข้อมูล ดัชนีชี้วัด เกี่ยวกับ
จำนวนผู้ป่วย ที่ Re Admit (กลับมา Admit ซ้ำ) ด้วย โรคเดิม ภายใน 28 วัน (ผู้ป่วยใน)
คำสั่ง SQL เขียนอย่างไรจ๊ะ
-
select op.an as an_1,v.regdate ,i1.name as icdname_1,v.pdx,d1.name as doctor_name1,
concat(p.pname,"",p.fname," ",p.lname) as ptname , v.hn,op2.an as an_2,v2.regdate ,op.vsttime as time_1,
op2.vsttime as time_2, i2.name as icdname_2,v2.pdx, d2.name as doctor_name2
from opitemrece op
left outer join an_stat v on v.an=op.an
left outer join ovst o on o.hn=v.hn and o.an > v.an and o.an is not null
left outer join an_stat v2 on v2.an=o.an
left outer join opitemrece op2 on o.an=op2.an and op2.an is not null
left outer join icd101 i1 on i1.code=v.pdx
left outer join icd101 i2 on i2.code=v2.pdx
left outer join doctor d1 on d1.code=v.dx_doctor
left outer join doctor d2 on d2.code=v2.dx_doctor
left outer join patient p on p.hn=o.hn
where v.dchdate between'2010-10-01'and'2010-12-31'
and (v.lastvisit_hour <=672 or v2.lastvisit_hour <=672) and v.pdx=v2.pdx
and v2.pdx not like "Z%" and v.pdx not like "Z%" and v2.pdx!="" and v.pdx!=""
group by v.hn
order by op.vstdate limit 200
ลองในฐานสำรองก่อนนะครับ
-
รอแป้บนะครับ
-
select a.hn,a.an,b.an,concat(p.pname,p.fname," ",p.lname)as ptname,b.pdx as old_pdx,a.pdx,a.dx0,a.dx1,a.dx2,a.dx3,a.dx4,a.dx5,
a.regdate,a.dchdate,b.regdate,b.dchdate,a.age_y,a.age_m,a.age_d,a.aid,w.name,i.name as pdxname,a.lastvisit
from an_stat a
left outer join an_stat b on a.hn=b.hn and a.pdx=b.pdx and a.an>b.an
left outer join patient p on a.hn=p.hn
left outer join icd101 i on i.code=a.pdx
left outer join ipt ip on ip.an=a.an
left outer join ward w on w.ward=a.ward
where (a.dchdate between "2011-06-15" and "2011-06-15" ) and a.lastvisit <= "28"
and ((to_days(a.regdate))-(to_days(b.dchdate)))<="28"
and a.pdx is not null and a.pdx<>"" and a.pdx not like "Z%"
order by a.an
ลองดูครับพี่นก
-
ขอบคุณมาก ๆ จ๊ะ
-
select a.hn,a.an,b.an,concat(p.pname,p.fname," ",p.lname)as ptname,b.pdx as old_pdx,a.pdx,a.dx0,a.dx1,a.dx2,a.dx3,a.dx4,a.dx5,
a.regdate,a.dchdate,b.regdate,b.dchdate,a.age_y,a.age_m,a.age_d,a.aid,w.name,i.name as pdxname,a.lastvisit
from an_stat a
left outer join an_stat b on a.hn=b.hn and a.pdx=b.pdx and a.an>b.an
left outer join patient p on a.hn=p.hn
left outer join icd101 i on i.code=a.pdx
left outer join ipt ip on ip.an=a.an
left outer join ward w on w.ward=a.ward
where (a.dchdate between "2011-06-15" and "2011-06-15" ) and a.lastvisit <= "28"
and ((to_days(a.regdate))-(to_days(b.dchdate)))<="28"
and a.pdx is not null and a.pdx<>"" and a.pdx not like "Z%" and a.old_diagnosis="Y"
order by a.an
เพื่อค้นหาว่ามาด้วยโรคเดิมครับ