Query Confusion

[tmcrouse]

I have a bit of a complex problem. I have a table that contains hospital admissions, skilled nursing admissions and hospice admissions.
There are also admission dates and discharge dates and the member patient number. There are 125715 rows that are not distinct. The table looks similiar to this.

id admit disch srvc
111 8/13/2010 8/18/2010 hospital
111 8/18/2010 8/20/2010 skilled nursing
111 8/20/2010 8/30/2010 hospice


222 7/1/2010 7/15/2010 hospital
333 9/19/2011 9/22/2011 hospital
333 9/26/2011 9/29/2011 hospital
333 9/29/2011 10/6/2011 skilled nursing

I need to separate out those that are really hospital such as id 222 and the 1 id 333 because the one that is 333 was discharged 9/22/2011 and the next admission is not until 9/26/2011. The ones that are skilled nursing but also hospital need to be in another table that have a disch date the same as admission date. So I should end up with 2 tables that look like this.

id admit disch srvc
222 7/2/2010 7/15/2010 hospital
333 9/19/2011 9/22/2011 hospital

the 2nd table

id admit disch srvc
111 8/13/2010 8/18/2010 hospital
111 8/18/2010 8/20/2010 skilled nursing
111 8/20/2010 8/30/2010 hospice
333 9/26/2011 9/29/2011 hospital
333 9/29/2011 10/6/2011 skilled nursing

Just not sure how to query this to make it work.

[jzwp11]

You do not need to separate the data into distinct tables, that will only complicate things. Your table is structured properly. You just need the correct query to extract the data you need.

First, you should create a query that extracts those admissions that have a srvc="hospital"


SELECT id, admit, disch, srvc
FROM yourtable
WHERE srvc="hospital"

From there we need to weed out the records that have an admission date equal to the disch date for a particular patient but where srvc<>"hospital". Now does it matter what the srvc is on the adjacent admission. You only mentioned skilled nursing admissions that were coincident with with a hospital discharge. Did you also need to weed out where a hospital discharge date was the same as a hospice admission?

[tmcrouse]

Not exactly. What I need is anything that is other than hospital like where there is a skilled nursing or hospice, that hospital stay needs to be along with the skilled nursing or hospice. So If I have

id admit disch srvc
111 8/13/2010 8/18/2010 hospital
111 8/18/2010 8/20/2010 skilled nursing
111 8/20/2010 8/30/2010 hospice
222 7/1/2010 7/15/2010 hospital
333 9/19/2011 9/22/2011 hospital
333 9/26/2011 9/29/2011 hospital
333 9/29/2011 10/6/2011 skilled nursing

then the only records I need for my hospital query are the ones that have no hospice or skilled nursing. So I only want ID 222 and then the 1 ID 333 that is a hospital admit 9/19 and disch 9/22.

All other records that are hospital, skilled nursing, hospice will be in a separate pull and all considered something other than hospital. This is because the disch date for instance on 111 that is 8/18 then has an admit to skilled nursing of 8/18 and same with the hospice. All those need to be in their own thing. There has to be a query for this because I sure as heck do not want to take 125k plus records and manually change the first line to skilled nursing since it coordinates to that so that I can end up just doing a query at the end that is =hospital and 2nd query <>hospital. I have to do it this way because I have a program of odds ratio I am using and if I just say give me all hospital those that went to hospice or skilled during the same time period or overlapping get counted 2 times. 1 time in hospital and 1 time in the skilled nursing which is not correct. if they have any item in their record other than a hospital only then they are counted as a skilled nursing or something not hospital.

[jzwp11]

I think I understand what you are saying.

Continuing with the query. We can start with this basic query to just isolate all records where srvc=hospital


SELECT id, admit, disch, srvc
FROM yourtable
WHERE srvc="hospital"

Now, we need to find out if there is an admission (for something other than hospital) on the same date as the hospital discharge. For that we need a subquery, so now our query looks like this (subquery shown in red):


SELECT myTable.ID, myTable.admit, myTable.disch, myTable.srvc, (SELECT top 1 Q1.admit FROM myTable as Q1 WHERE srvc<>"hospital" and q1.admit=mytable.disch and Q1.ID=myTable.ID) as OtherAdmission
FROM myTable
WHERE (((myTable.srvc)="hospital"));

So if a record shows an admit date in the OtherAdmission field then that record would not count as a hospital admission. Using your data presented earlier, the query above will yield the following:

ID	admit	disch	srvc	OtherAdmission
111	8/13/2010	8/18/2010	hospital	8/18/2010
222	7/1/2010	7/15/2010	hospital
333	9/19/2011	9/22/2011	hospital
333	9/26/2011	9/29/2011	hospital	9/29/2011

Now we can create a query based on the above query that ignores those records with a date in the OtherAdmission field to get the hospital data you are after. That query would look like this

SELECT qryRawHospitalData.ID, qryRawHospitalData.admit, qryRawHospitalData.disch, qryRawHospitalData.srvc
FROM qryRawHospitalData
WHERE (((qryRawHospitalData.OtherAdmission) Is Null));

I've attached an example database that includes the above queries.

[tmcrouse]

what does the select top 1 in the subquery mean? can I leave that out if I have more than one admissions other than an inpatient? Meaning if it is like

111 1/1/2010 1/5/2010 hospital
111 1/5/2010 1/15/2010 nurse
111 1/15/2010 1/20/2020 rehab

So this means there are 2 others associated with the hospital. So I would think I do not need the top 1. Not sure what the top 1 is doing.

[jzwp11]

A subquery within a SELECT clause can only return 1 record. So if there just happened to be both a skilled nursing and a hospice admission with the same admission date as the hospital discharge date that would cause a problem since 2 records could be returned. From what you said, you only cared whether any non-hospital admission occurred on the hospital discharge date. If you take out the Top 1, the query will error out (if 2 non-hospital events occurred on the discharge date for a patient).

111 1/1/2010 1/5/2010 hospital
111 1/5/2010 1/15/2010 nurse
111 1/15/2010 1/20/2020 rehab

What you describe above is different from what you described earlier. You are basically saying that you may have several cascading events related to a hospital stay (the rehab admission event does not start on the hospital discharge date but rather depends on the intermediate nursing event). I think that may require a different table structure but without knowing more about your application that is hard to say.

[tmcrouse]

I am just throwing other things into the mix because there are 125k+ records and to determine what all is in here I would have to go thru all of them.

[tmcrouse]

The table structure is set as it is because it comes from our claims database. So I cannot change the way it appears. I guess I have to go through all 125k+ rows and see if there is anything other than 1 other associated with a hospital. If so I will get back to you.

[tmcrouse]

Ok just ran into one like I described.

id admit disch srvc
111 27sep201 30sep2010 hospital
111 30sep2010 09dec2010 nursing
111 10dec2010 18oct2010 hospice

I know I can put in the subquery a disch_dt+1 or whatever the PM's say they want to look at. I am sure that this will qualify to go into the other pile. As in this whole ID's set of admissions. I am going to try running the query just as the top 1 and see what it gives me. I tried just saying = hospital and doing my readmit program like that but then it was counting other admissions that should fall into other becuase 111 does have hospital admission. And that hospital admission really needs to be in the other because they went somewhere other than hospital after surgery.

[jzwp11]

The table structure is set as it is because it comes from our claims database. So I cannot change the way it appears.

Who said that you could not take the claims data you get and put it into another structure to do your analysis?

Does your current table have a field that uniquely identifies the record (i.e. a primary key field)?

If not, you could dump your data into a table that assigns a unique record ID. For example purposes, let's call this table tblAdmissions and the primary key field is called pkAdmissionID. The other fields will remain the same. Now create the following table:



tblRelatedAdmissions
-pkRelatedAdmitID primary key, autonumber
-fkPAdmissionID foreign key to your main admission table
-fkSAdmissionID foreign key to your main admission table

You could populate the above table using an append query that in turn would be based on a query very similar to the one I provided (i.e. the one with the subquery)