Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Everyone,
I've been struggling with this problem for quite some time now, which in my head seems to be simple but I can't figure out a solution.
I have two tables loaded in my app: Admissions and Appointments.
Admissions contains the records of when the patient was admitted at the hospital. Here is a simplified version of the table:
Admission ID | Patient ID | Admission Date |
a1 | p1 | 01/01/2020 |
a2 | p1 | 02/01/2020 |
a3 | p2 | 02/01/2020 |
Appointments contains the appointments each patient attended in a clinic
Appointment ID | Patient ID | Appointment Date |
app1 | p1 | 01/01/2019 |
app2 | p3 | 01/03/2020 |
app3 | p4 | 02/10/2020 |
I want to check the against each admission the Previous appointment this patient attended, by comparing the Admission date with the Appointment Date. Ideally by making a reference to the AppointmentID in an additional column while loading the Admissions table.
Can anybody give me some direction?
This should add a [Previous Appointment ID] to your Admissions table based on the most recent Appointment for a patient in the Appointments table prior to the patients admissions data. (It is only tested on your limited data set, so you may need to tweak it, but the concept should work. Also, you may want to drop the [Previous Appointment Date] from the Admissions table if it is not needed.)
AdmissionsTemp:
Load [Patient ID],
[Admission Date]
Resident Admissions;
Left Join (AdmissionsTemp)
Load [Patient ID],
[Appointment Date]
Resident Appointments;
AdmissionsTemp2:
Load [Patient ID],
[Admission Date],
Date(max([Appointment Date])) as [Previous Appointment Date]
Resident AdmissionsTemp
where [Appointment Date] < [Admission Date]
group by [Patient ID],[Admission Date];
Left Join (AdmissionsTemp2)
Load [Patient ID],
[Appointment Date] as [Previous Appointment Date],
[Appointment ID] as [Previous Appointment ID]
Resident Appointments;
Join (Admissions)
Load [Patient ID],
[Admission Date],
[Previous Appointment ID]
Resident AdmissionsTemp2;
Drop Tables AdmissionsTemp, AdmissionsTemp2;
This should add a [Previous Appointment ID] to your Admissions table based on the most recent Appointment for a patient in the Appointments table prior to the patients admissions data. (It is only tested on your limited data set, so you may need to tweak it, but the concept should work. Also, you may want to drop the [Previous Appointment Date] from the Admissions table if it is not needed.)
AdmissionsTemp:
Load [Patient ID],
[Admission Date]
Resident Admissions;
Left Join (AdmissionsTemp)
Load [Patient ID],
[Appointment Date]
Resident Appointments;
AdmissionsTemp2:
Load [Patient ID],
[Admission Date],
Date(max([Appointment Date])) as [Previous Appointment Date]
Resident AdmissionsTemp
where [Appointment Date] < [Admission Date]
group by [Patient ID],[Admission Date];
Left Join (AdmissionsTemp2)
Load [Patient ID],
[Appointment Date] as [Previous Appointment Date],
[Appointment ID] as [Previous Appointment ID]
Resident Appointments;
Join (Admissions)
Load [Patient ID],
[Admission Date],
[Previous Appointment ID]
Resident AdmissionsTemp2;
Drop Tables AdmissionsTemp, AdmissionsTemp2;
Hi Gary,
thanks for the solution. that worked perfectly!