Readmissions

[NiyiYA]

Hi

I am struggling with an MS SQL query to get readmissions, with the the difference between the last discharge date and new admission date less than 28 days
this is what I have done so far and it hasn't worked

select A.PROVIDERADMISSION_NUMBER,A.DISCH_DATE,B.ADMIN_DATE,DATEDIFF(D,A.DISCH_DATE,B.ADMIN_DATE)
from dbo.WIP A,dbo.WIP2 B
where A.ID=B.ID
AND A.HOSPITAL = B.HOSPITAL
AND A.DISCH_DATE < B.ADMIN_DATE
AND A.PROVIDERADMISSION_NUMBER < B.PROVIDERADMISSION_NUMBER
AND DATEDIFF(D,A.DISCH_DATE,B.ADMIN_DATE) < 28

I keep getting multiple references to the same discharge date

All help appreciated

[HansV]

Welcome to Eileen's Lounge!

Your query is looking at ALL discharge dates between 1 and 27 days before an admission date. Do you want to consider only the most recent discharge date before an admission date?

[NiyiYA]

Hi Hans

Yeah,

But just to clarify what I am trying to find is the interval between the dischrge dates and admission dates which are less than 27 days within a year

Thanks

[HansV]

You need to do this in two steps:

First, a totals query that retrieves the most recent discharge date before an admission date for the same ID and Hospital:

SELECT A.ID, A.HOSPITAL, Max(A.DISCH_DATE) AS MaxOfDISCH_DATE, B.ADMIN_DATE
FROM dbo.WIP AS A, dbo.WIP2 AS B
WHERE (((A.PROVIDERADMISSION_NUMBER)<.[PROVIDERADMISSION_NUMBER]) AND ((A.ID)=.[ID]) AND ((A.HOSPITAL)=.[HOSPITAL]) AND ((A.DISCH_DATE)<.[ADMIN_DATE]))
GROUP BY A.ID, A.HOSPITAL, B.ADMIN_DATE;

Save this as (for example) qryMaxDischargeDate.

Then, a query that retrieves only the most recent discharge dates at most 28 days before the admission date:

SELECT qryMaxDischargeDate.ID, qryMaxDischargeDate.HOSPITAL, qryMaxDischargeDate.MaxOfDISCH_DATE, qryMaxDischargeDate.ADMIN_DATE, DateDiff("d",[MaxOfDISCH_DATE],[ADMIN_DATE]) AS d
FROM qryMaxDischargeDate
WHERE (((DateDiff("d",[MaxOfDISCH_DATE],[ADMIN_DATE]))<28));


(I used MS Access, so syntax may be slightly different - Access requires quotes around the first argument of DateDiff)

See the attached sample Access database (zipped).

Admissions.zip

[NiyiYA]

It works but doesn't allow for multiple assessment within the year

[HansV]

As far as I can tell, it does in the sample database, but apparently I haven't interpreted the situation correctly. Could you try to explain in words what the tables mean, and what exactly you want to accomplish?