run ในสายฟ้าได้ แต่สร้าง report ไม่ได้ ช่วยดูให้หน่อยครับ
DateTime : 2011-07-21 17:33:24
Post By : kroong
IP Address : 180.180.166.221
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'2011-03-01'and'2011-03-31'
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
Message !!
หากต้องการตอบกระทู้ Webboard กรุณาเข้าใช้ระบบก่อนนะค่ะ..
กระทู้ตอบกลับ
คิวรี่ ในรีพอร์ตมีการจำกัดการใช้งานหลายๆอย่างครับ คาดว่าอาจารย์ คงอยากให้เขียน sql ให้รัดกุมครับ
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) as ch,
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'2011-03-01'and'2011-03-02'
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 ch>1
order by op.vstdate
Post โดย : decepticons Date: 2011-07-22 00:48:00 ip: 203.113.118.74
ว่าแต่รายงานนี้เอาไปใช้เกี่ยวกบอะไรครับพี่
Post โดย : decepticons Date: 2011-07-22 00:49:47 ip: 203.113.118.74
revisit 48 ชั่วโมง diag เดิม
จะแก้ไขได้ป่าวครับ... ให้ สร้าง report ได้
Post โดย : kroong Date: 2011-07-22 10:33:52 ip: 180.180.163.106
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) as ch,
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'2011-03-01'and'2011-03-02'
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 ch>1
order by op.vstdate
Post โดย : decepticons Date: 2011-07-22 11:42:35 ip: 203.113.118.78
สร้าง report ได้แล้วครับ ขอบคุณมากครับ
Post โดย : kroong Date: 2011-07-22 17:51:48 ip: 125.26.115.46