Compare dates between two tables in the script

[eugabgomes]

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? 

[GaryGiles · Accepted solution]

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;

[eugabgomes]

Hi Gary,

thanks for the solution. that worked perfectly!