use a self join to find all recurrence within 30 days?

[seagreen]

Hello,

I think I could use a self join to find each patients' admissions which can be considered readmissions within 30 days but I am wondering how to make sure that the records are compared with each properly. For example, if a patient has 5 total admissions within a year, the first one is obviously not a RE-admission for that year but I want to look at each subsequent pair of records to see if the subsequent admission is within 30 days of the most recent discharge. How could I write a SQL statement (SQL view in MS access) to do this or must I export all the records to Excel to calculate this.

Thanks in advance for your time and help. Please let me know if I need to clarify what I've described.

 

[dk]

Hi

Without seeing an example of your data and expected results it's difficult to know exactly what you need, however you might be able to use something like this.

Assuming your table has 2 columns: PATIENT_ID and ADMISSION_DATE and here is some example data

<table>
<tr><TD>PATIENT_ID</td><td>ADMISSION_ID</td></tr>
<tr><TD>1</td><td>1-Jan-09</td></tr>
<tr><TD>1</td><td>1-Mar-09</td></tr>
<tr><TD>1</td><td>15-Mar-09</td></tr>
<tr><TD>1</td><td>1-Jun-09</td></tr>
<tr><TD>2</td><td>1-May-09</td></tr>
<tr><TD>3</td><td>15-Mar-09</td></tr>
<tr><TD>3</td><td>16-Mar-09</td></tr>
<tr><TD>3</td><td>17-Mar-09</td></tr>
</table>

If you use this SQL (which uses a correlated subquery rather than a self join):

SELECT A.PATIENT_ID, A.ADMISSION_DATE,
	 (SELECT COUNT(*) FROM ADMISSIONS B WHERE 
		B.PATIENT_ID=A.PATIENT_ID AND 
		B.ADMISSION_DATE>=(A.ADMISSION_DATE-30) AND 
		B.ADMISSION_DATE<A.ADMISSION_DATE) AS ADMISSIONS_WITHIN_30_DAYS
	FROM ADMISSIONS AS A;

You'll get the following result:

<table>
<tr><td>PATIENT_ID</td><td>ADMISSION_DATE</td><td>ADMISSIONS_WITHIN_30_DAYS</td></tr>
<tr><td>1</td><td>01-Jan-09</td><td>0</td></tr>
<tr><td>1</td><td>01-Mar-09</td><td>0</td></tr>
<tr><td>1</td><td>15-Mar-09</td><td>1</td></tr>
<tr><td>1</td><td>01-Jun-09</td><td>0</td></tr>
<tr><td>2</td><td>01-May-09</td><td>0</td></tr>
<tr><td>3</td><td>15-Mar-09</td><td>0</td></tr>
<tr><td>3</td><td>16-Mar-09</td><td>1</td></tr>
<tr><td>3</td><td>17-Mar-09</td><td>2</td></tr>
</table>

Have a play around and see if that works for you. If not, can you provide some example data along with expected output.



DK

 

[seagreen]

Thanks dk! I will work with this when I get to my PC at work. Should I sort the table by patient by ascending admission_date before I run your query?
I will post again in a couple of hours when I have tried this.
Cheers!

 

[dk]

Hi

I just noticed but for some reason the board seems to have a problem with displaying what I originally posted. The SQL should be this:

SELECT A.PATIENT_ID, A.ADMISSION_DATE, (SELECT COUNT(*) FROM ADMISSIONS B WHERE B.PATIENT_ID=A.PATIENT_ID AND B.ADMISSION_DATE>=(A.ADMISSION_DATE-30) AND
B.ADMISSION_DATE < A.ADMISSION_DATE) AS ADMISSIONS_WITHIN_30_DAYS FROM ADMISSIONS A


Re your question, you can sort within the query if you want e.g. just add an ORDER BY A.PATIENT_ID at the end of the SQL above. Like i said, have a play around and see what works for you and let me know if you get stuck.

Cheers
DK

 

[seagreen]

Thanks dk! I am running the query now and will post later with results.

 

[seagreen]

I'm still working on this one - will post again Monday. I am getting higher numbers than I should. I'm using a couple of pass-through queries (one does work prior to the work I asked about) to a SQL Server DB and the queries are SLOWWWW. Not because of the elegant code you've provided but because there aren't any indexes and the record count is huge (I don't have privileges to create indexes. I just saw that I could set a recordlimit using a drop down box in Access but didn't discover that 'til late today. Not enough time to work on this today and each run took > 1.5 hours so not much experimenting/iterations possible yet. I WILL post the definitive answer with examples if I get one so that others may use later. What I am doing is a very common task but anyone I know just dumps all the data to Excel and does a lot of manipulation. I'm hoping to learn the least tedious way! Thanks dk!

 

[dk]

Hi

The query that I posted will definitely run slowly for a large number of records. The problem is that the "inner query" is being run once for every single record in the table e.g. if your table has 1m records it will effectively be running 1 million queries! An index on the admission_date column would drastically improve performance.....just an idea but something I have done in the past is create a temporary table that is basically a copy of the original data, add the necessary indexes and run the queries based on the new table. The time taken do this extra work would most likely be much quicker than running the query on the unindexed table. I'm guessing though that if you don't have create table priveleges on the SQL database so you'd need to create a temporary table in Access.

I'll have a think and see if I can find a better way to structure the query - maybe an inner join would be more efficient. I'll do some testing and post back if I find a quicker way.

DK

 

[seagreen]

Hi

The query that I posted will definitely run slowly for a large number of records. The problem is that the "inner query" is being run once for every single record in the table e.g. if your table has 1m records it will effectively be running 1 million queries! An index on the admission_date column would drastically improve performance.....just an idea but something I have done in the past is create a temporary table that is basically a copy of the original data, add the necessary indexes and run the queries based on the new table. The time taken do this extra work would most likely be much quicker than running the query on the unindexed table. I'm guessing though that if you don't have create table priveleges on the SQL database so you'd need to create a temporary table in Access.

I'll have a think and see if I can find a better way to structure the query - maybe an inner join would be more efficient. I'll do some testing and post back if I find a quicker way.

DK

Thanks DK! True, I don't have any privileges on the sql server. It took me forever just to get ODBC access - most have to use the GUI point and click query tool, download the data in a text file and import it into Access. I agree using temporary tables is great for massaging the data and winnowing it down (and being able to add indexes, keys, etc.). I don't know a way to say select field1, field2, ... INTO tblTEMP while doing a pass through query though (to make a temp table). I will definitely post the pass through queries that I am using, on Monday. I cannot access the SQL Server from home else I would work on it this weekend.
Thanks again.