Calculating Difference Between Dates in Different Rows

[DEB]

Hope anyone can assist -


I currently have a table that has patient ID (unique record (SSN)) and a date of visit field. The SSN may have numerous rows with different dates of visits. What I am trying to do is a DateDiff function that will retrieve records that are 48 hours apart or 2 days apart. We are tracking patients that return within 48 hours of a previous visit. I am having a day!!! Any help would be greatly appreciated.
Thanks.
Deb

[pbaldy]

Try this type of thing:

http://allenbrowne.com/subquery-01.html#AnotherRecord

[DEB]

ok, I tried and and I got nothin! No go, any other wisdom??

[pbaldy]

No, that method has always worked for me. "got nothin" doesn't give anybody much to go on to see where you may have gone wrong.

[DEB]

ok I tried this several different ways, the first part is pulling in a sect of data for the ER, and then I tried the meter reading one and it was not what I needed so I thought I could add the WHERE IN on the end of this but could not, is there another way? Sorry......

SELECT S562_PATIENT.SSN, S562_OutpatientEncounter.AMIS_STOP_CODE, S562_OutpatientEncounter.ENCOUNTER_DATE_DT, S562_OutpatientEncounter.CLINIC_STOP_CODE
FROM S562_OutpatientEncounter INNER JOIN S562_PATIENT ON S562_OutpatientEncounter.PATIENT_IEN = S562_PATIENT.PATIENT_IEN
GROUP BY S562_PATIENT.SSN, S562_OutpatientEncounter.AMIS_STOP_CODE, S562_OutpatientEncounter.ENCOUNTER_DATE_DT, S562_OutpatientEncounter.CLINIC_STOP_CODE
HAVING (S562_OutpatientEncounter.AMIS_STOP_CODE =130 AND S562_OutpatientEncounter.ENCOUNTER_DATE_DT >#11/30/2011#)


****this is where I am stuck.....

and WHERE S562_PATIENT.SSN IN
(SELECT TOP 3
FROM S562_PATIENT.SSN AS Dupe
WHER Dupe.SSN = S562_PATIENT.SSN);

[pbaldy]

What's the goal? I thought it was to identify the time between visits. I'd use the meter example to get the previous record on the same line as the current record. Then you can use DateDiff() to calculate the time between them, and filter accordingly. The one you posted at the end (based on his Top N records per group) would be more appropriate for finding the last 3 visits for each patient or something along those lines.

[DEB]

The goal is to find any SSN that was seen more than once in a 48 hour period so perhaps I will try the meter example again and see what happens...Txs

[DEB]

Still stuck...I am lost at the end of the string...where does the Dupe fit in?

SELECT S562_PATIENT.SSN, S562_OutpatientEncounter.AMIS_STOP_CODE, S562_OutpatientEncounter.ENCOUNTER_DATE_DT
FROM S562_OutpatientEncounter INNER JOIN S562_PATIENT ON S562_OutpatientEncounter.PATIENT_IEN = S562_PATIENT.PATIENT_IEN
HAVING (((S562_OutpatientEncounter.AMIS_STOP_CODE)=130) AND ((S562_OutpatientEncounter.ENCOUNTER_DATE_DT)>#11/30/2011#))
(SELECT TOP 1 Dupe.SSN
FROM S562_PATIENT.SSN AS Dupe
WHERE Dupe.SSN = S562_PATIENT.SSN
AND Dupe.ENCOUNTER_DATE_DT < S562_OutpatientEncounter.ENCOUNTER_DATE_DT;

[pbaldy]

You would need an ORDER BY clause in the subquery to make sure the next most recent visit was pulled. Can you post the db, or a representative sample (without real SSN's of course)?

[DEB]

we have several tables in a Data Warehouse.

The two tables in which I am pulling from are (S562_PATIENT) which contains the SSN field and (S562_OutpatientEncounter) which contains the ENCOUNTER_DATE_DT and AMIS_STOP_CODE (which is a 3 digit number that determines the hospital location such as the ER) the two tables have a PATIENT_IEN (a unique number that is used for the relationship tie).

[DEB]

the below query is how I pull in the first set of data

was supposed to look like a grid, but pasted differently..sorry

SSN AMIS_STOP_CODE ENCOUNTER_DATE_DT CLINIC_STOP_CODE
9 # digits no dashes 130 12/19/11 1:49 PM EMERGENCY DEPT


SELECT S562_PATIENT.SSN, S562_OutpatientEncounter.AMIS_STOP_CODE, S562_OutpatientEncounter.ENCOUNTER_DATE_DT, S562_OutpatientEncounter.CLINIC_STOP_CODE
FROM S562_OutpatientEncounter INNER JOIN S562_PATIENT ON S562_OutpatientEncounter.PATIENT_IEN = S562_PATIENT.PATIENT_IEN
GROUP BY S562_PATIENT.SSN, S562_OutpatientEncounter.AMIS_STOP_CODE, S562_OutpatientEncounter.ENCOUNTER_DATE_DT, S562_OutpatientEncounter.CLINIC_STOP_CODE
HAVING (((S562_OutpatientEncounter.AMIS_STOP_CODE)=130) AND ((S562_OutpatientEncounter.ENCOUNTER_DATE_DT)>#11/30/2011#));