' 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
mBase2012
SELECT a.REG_DATETIME , a.HN ,a.VISIT_ID, c.ICD10_TM, b.DXT_ID,
(TO_DAYS(a.REG_DATETIME)* 24) as TO_DAY, (TIME_TO_SEC(time(a.REG_DATETIME))/3600) as TIME
FROM opd_visits a
INNER JOIN opd_diagnosis b ON a.visit_id = b.visit_id and b.is_cancel = 0
INNER JOIN icd10new c ON b.icd10 = c.ICD10 AND b.DXT_ID = 1
#หารหัส copd j440-j449
AND c.ICD10_TM BETWEEN 'j440' AND 'j449'
WHERE a.REG_DATETIME > '2018.10.20 00:01'
AND a.is_cancel = 0
ORDER BY a.HN
LIMIT 2
****************Re-Admit28วัน**********************
SELECT ip1.visit_id as vn1, date(ip1.adm_dt) as adm1, time(ip1.adm_dt) as time1 , c.hn , ip1.adm_id as an1,i1.icd10_tm as icd1, count(c.hn),
ip2.visit_id as vn2,ip2.adm_id as an2,date(ip2.adm_dt) as adm2, time(ip2.adm_dt) as time2,i2.icd10_tm as icd2,
#((to_days(o2.REG_DATETIME)*24)- ((to_days(o1.REG_DATETIME)*24)) )
(date(o2.REG_DATETIME)- (date(o1.REG_DATETIME)))
as revist_time
FROM opd_visits o1
INNER JOIN cid_hn c ON o1.hn = c.hn
INNER JOIN ipd_reg ip1 ON o1.visit_id = ip1.visit_id and ip1.is_cancel = 0
LEFT OUTER JOIN opd_diagnosis dx1 ON ip1.visit_id = dx1.visit_id AND dx1.dxt_id = 1 AND dx1.is_cancel = 0
LEFT OUTER JOIN icd10new i1 ON dx1.icd10 = i1.icd10
INNER JOIN opd_visits o2 ON o2.hn = c.hn AND o2.is_cancel = 0
LEFT OUTER JOIN ipd_reg ip2 ON o2.visit_id = ip2.visit_id AND ip2.is_cancel = 0
LEFT OUTER JOIN opd_diagnosis dx2 ON ip2.visit_id = dx2.visit_id AND dx2.dxt_id = 1 AND dx2.is_cancel = 0 AND dx2.icd10 is not null
LEFT OUTER JOIN icd10new i2 ON dx2.icd10 = i2.icd10 AND i1.icd10_tm = i2.icd10_tm
WHERE o1.REG_DATETIME BETWEEN '2020-01-01 00:00' AND '2021-03-31 23:59'
#AND ((to_days(o2.REG_DATETIME)*24)- ((to_days(o1.REG_DATETIME)*24))) <=28
AND (date(o2.REG_DATETIME)- (date(o1.REG_DATETIME))) <=28
AND ip2.visit_id > ip1.visit_id
AND i1.icd10_tm = i2.icd10_tm
AND ip1.is_cancel = 0
GROUP BY c.hn
HAVING count(c.hn)>1
****************Re- Visit48วัน**********************
select o1.visit_id as vn1,date(o1.REG_DATETIME) as d1,time(o1.REG_DATETIME) as time_1,i1.icd10_tm as icdname_1,#d1.name as doctor_name1,
concat(trim(p.fname)," ",p.lname) as ptname ,
c.hn,count(c.hn),
o2.visit_id as vn_2,date(o2.reg_datetime) as d2 ,time(o2.REG_DATETIME) as time_2, i2.icd10_tm as icdname_2, #d2.name as doctor_name2 ,
(((to_days(o2.REG_DATETIME)*24)- ((to_days(o1.REG_DATETIME)*24)) + (( time_to_sec(o2.REG_DATETIME))/3600)) - (( time_to_sec(o1.REG_DATETIME))/3600))
as revist_time
from opd_visits o1
LEFT OUTER JOIN cid_hn c on o1.hn = c.hn AND o1.is_cancel = 0
LEFT OUTER JOIN population p on p.cid = c.cid
LEFT OUTER JOIN opd_visits o2 on o2.hn = c.hn AND o2.is_cancel = 0
#left outer join vn_stat v2 on v2.vn=o.vn
LEFT OUTER JOIN opd_diagnosis dx1 on o1.visit_id = dx1.visit_id AND dx1.DXT_ID = 1 AND dx1.is_cancel = 0
LEFT OUTER JOIN opd_diagnosis dx2 on o2.visit_id = dx2.visit_id AND dx2.DXT_ID = 1 AND dx2.is_cancel = 0
LEFT OUTER JOIN icd10new i1 on i1.icd10 = dx1.icd10
LEFT OUTER JOIN icd10new i2 on i2.icd10 = dx2.icd10
#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 o1.REG_DATETIME between'2021-01-01 00:00' AND '2021-05-30 23:59'
AND o2.visit_id > o1.visit_id
AND i1.icd10_tm = i2.icd10_tm AND left(i1.icd10_tm,1) not in ('Z','U')
AND (((to_days(o2.REG_DATETIME)*24)- ((to_days(o1.REG_DATETIME)*24)) + (( time_to_sec(o2.REG_DATETIME))/3600)) - (( time_to_sec(o1.REG_DATETIME))/3600)) between 0.001 and 48
group by c.hn
having count(c.hn)>1
#order by op.vstdate
ไม่มีความคิดเห็น:
แสดงความคิดเห็น