Hi All,
I've posted several times before and I just want so say thanks for the help I've gotten previously, it's much appreciated. I work in the healthcare industry. I'm trying to calculate the proportion of people that return to the ED within 7 days of their last visit and get admitted. I've managed to create a table that uses a lag statement that looks at a current records ED Visit Date and compares it to the previous ED Visit Date for a given individual and if it's within 7 days then I flag the return to ED within 7 days. If that return within 7 days has an ED Disposition of "Admitted" then they get flagged in the table as a return to ED within 7 Days with Admission. The only issue is I've been encountering situations where someone has two return to ED with Admission records for a given initial visit, as you'll see in the table below. I only want to pull the Return to ED within 7 Days with Admission that is the closest to the initial ED Visit Record or in other words the Admission Record closest in time to the initial ED Visit within the episode, if that makes sense. Below is a subset of records I've completely anonymized from a table called ED_VISITS where you can see this:
ED_RECORD_ID PATIENT_ID HOSPITAL_ID ED_VISIT_DT ED_DISPOSITION RETURN_ED_7_DAYS RETURN_ED_7_DAYS_ADMITTED
1 1 115 10-Dec-2012 1:36 PM Discharged
2 1 115 15-Dec-2012 10:46 PM Admitted Y Y
3 1 47 22-Dec-2012 12:36 PM Admitted Y Y
4 2 28 20-May-2012 9:19 AM Discharged
5 2 28 21-May-2012 1:07 PM Admitted Y Y
6 2 21 26-May-2012 11:37 AM Admitted Y Y
7 3 80 11-Mar-2011 4:31 PM Discharged
8 3 80 12-Mar-2011 7:01 AM Discharged Y
9 3 80 12-Mar-2011 6:09 PM Discharged Y
10 3 80 13-Mar-2011 9:30 AM Discharged Y
11 3 80 13-Mar-2011 5:12 PM Admitted Y Y
12 3 42 15-Mar-2011 7:55 PM Admitted Y Y
13 4 25 24-Sep-2012 6:15 AM Discharged
14 4 19 24-Sep-2012 2:44 PM Admitted Y Y
15 4 170 30-Sep-2012 3:33 PM Admitted Y Y
Not only do I need only grab one Return to ED within 7 days with Admission Record for the patient, I also need to grab that records ED_RECORD_ID which I'll use to pull other data elements from the source table afterwards to do some analysis and groupings. Hopefully this makes sense, if it doesn't please feel free to ask away and I'll do my best to answer the questions. I'm using Oracle 10g.
Thanks
Edited by: spalato76 on Mar 29, 2013 11:35 AM