Readmit ใน 28 วันครับ มีสองแบบ

[chiaw nonghan]

Readmit ใน 28 วันครับ มีสองแบบ ช่วงสิ้นปีมีหลายหน่วย ขอข้อมูลเยอะครับ ช่วยตรวจสอบ code ด้วยนะครับ

จำนวน re admit  28 วัน(ครั้ง)

select av2.an as an2,av2.regdate as reg2,av2.dchdate as dch2,a.an,a.hn,concat(p.pname,p.fname,'  ',p.lname) as ptname,
a.regdate,a.dchdate,a.lastvisit,
a.pdx,a.age_y,a.dx0,a.dx1,a.dx2,a.dx3,a.dx4,a.dx5,a.income,a.pttype
from an_stat a
left outer join patient p on p.hn=a.hn
inner join (select a2.an,a2.hn,a2.regdate,a2.dchdate,a2.lastvisit
            from an_stat a2
            where a2.dchdate between '2007-08-01' and '2007-08-31'
            and a2.lastvisit <= 28)
            as av2 on av2.hn=a.hn
where (av2.regdate=a.regdate-a.lastvisit or av2.regdate=a.regdate)
order by a.hn,av2.an

readmit พร้อม an ครั้งก่อนด้วย
select DISTINCT avn.an,hn,ptname,reg_date,dch,lastvisit ,pdx,age_y,pdx,dx0,dx1,dx2,dx3,dx4,dx5
 from (select av2.an as an2,a.an,a.hn,concat(p.pname,p.fname,'  ',p.lname) as ptname,
       a.regdate as reg_date,
       date_format(av2.regdate,'%j')-date_format(a.regdate,'%j') as reg_diff1,
       a.dchdate as dch,a.lastvisit,
       av2.regdate as reg_date2,av2.dchdate as dch2,av2.lastvisit as lv2,
              a.pdx,a.age_y,a.dx0,a.dx1,a.dx2,a.dx3,a.dx4,a.dx5,a.income,a.pttype
       from an_stat a
       left outer join patient p on p.hn=a.hn
       left outer join (select a2.an,a2.hn,a2.regdate,a2.dchdate,a2.lastvisit
                   from an_stat a2 where a2.dchdate between '2007-08-01' and '2007-08-31' and a2.lastvisit<=28  )
                   as av2 on av2.hn=a.hn) as avn
where reg_diff1=0 or reg_diff1=lv2 or reg_diff1=lv2+1
order by avn.hn,avn.an

[rtumayo]

select a.hn,a.an,a.regdate,a.dchdate,a.lastvisit,a.pdx,a.age_y,i.name as pdxname,
concat(p.pname,"",p.fname,"  ",p.lname) as ptname
from an_stat a
left outer join patient p on p.hn=a.hn
left outer join icd101 i on i.code=substring(a.pdx,1,3)

where a.ward="01"   and  a.dchdate between "2005-10-01" and "2005-10-30" and
a.lastvisit <= 28