ลองแก้ไข+เพิ่มเติม เป็นรายงาน revisit in 48 hr. กับ เลือก diag 2 แบบ - diag เดียวกัน กับ คนละ diag ในแต่ละ revisit
' revisit in 48 hr, diag เดียวกัน
select op.vn as vn_1,op.vstdate as d1,i1.name as icdname_1,d1.name as doctor_name1,
concat(p.pname,"",p.fname," ",p.lname) as ptname ,
v.hn,count(v.hn),
op2.vn as vn_2,op2.vstdate as d2,op.vsttime as time_1,op2.vsttime as time_2, i2.name as icdname_2, d2.name as doctor_name2 ,
(((to_days(op2.vstdate)*24)- ((to_days(op.vstdate)*24)) + (( time_to_sec(op2.vsttime))/3600)) - (( time_to_sec(op.vsttime))/3600))
as revist_time
from opitemrece op
left outer join vn_stat v on v.vn=op.vn
left outer join ovst o on o.hn=v.hn and o.vn > v.vn and o.vn is not null
left outer join vn_stat v2 on v2.vn=o.vn
left outer join opitemrece op2 on o.vn=op2.vn and op2.vn 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 op.vstdate between'2009-03-01'and'2009-03-01'
and (((to_days(op2.vstdate)*24)- ((to_days(op.vstdate)*24)) + (( time_to_sec(op2.vsttime))/3600)) - (( time_to_sec(op.vsttime))/3600)) between 0.001 and 48
and v.pdx=v2.pdx
group by v.hn
having count(v.hn)>1
order by op.vstdate
...............................................................
' revisit in 48 hr, คนละ diag
select op.vn as vn_1,op.vstdate as d1,i1.name as icdname_1,d1.name as doctor_name1,
concat(p.pname,"",p.fname," ",p.lname) as ptname ,
v.hn,count(v.hn),
op2.vn as vn_2,op2.vstdate as d2,op.vsttime as time_1,op2.vsttime as time_2, i2.name as icdname_2, d2.name as doctor_name2 ,
(((to_days(op2.vstdate)*24)- ((to_days(op.vstdate)*24)) + (( time_to_sec(op2.vsttime))/3600)) - (( time_to_sec(op.vsttime))/3600))
as revist_time
from opitemrece op
left outer join vn_stat v on v.vn=op.vn
left outer join ovst o on o.hn=v.hn and o.vn > v.vn and o.vn is not null
left outer join vn_stat v2 on v2.vn=o.vn
left outer join opitemrece op2 on o.vn=op2.vn and op2.vn 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 op.vstdate between'2009-03-01'and'2009-03-01'
and (((to_days(op2.vstdate)*24)- ((to_days(op.vstdate)*24)) + (( time_to_sec(op2.vsttime))/3600)) - (( time_to_sec(op.vsttime))/3600)) between 0.001 and 48
and v.pdx<>v2.pdx
group by v.hn
having count(v.hn)>1
order by op.vstdate
..........................................................................
// ลองรันดูนะครับ
ผมลองแล้วแต่ยังไม่ได้ลองทานสอบกับ database แบบ manual ดู
หัวข้อ: รายงาน Re-visit ใน 24 ชั่วโมง (อ่าน 20129 ครั้ง)