Question on Merging Two Tables with Approximate Matches Instead of Exact Matches

[AnAverageDave]

Hi, the problem I am currently trying to solve requires some background, so please bear with me:

I am a medical student working on a retrospective study of some medical records, and one piece of data that I am collecting is time spent in the operating room for a specific operation. All of the other information that I am collecting comes from "program X" (our hospital's medical record software) where I will be manually reading through notes to find the information that I need. However, this one piece of information is stored on "program Y" (medical record software specific to the operating rooms). Unfortunately, I do not have access to "program Y," so I talked to our medical records people, and their solution was to have the program dump the relevant information into an Excel table that they were able to give me. The problem is that what they were able to give me was a list of every procedure done at our hospital for the time span that I indicated, and that added up to just under 76,000 entries. Only 2,400 of those entries are ones that I need. Now I need to correlate the data.

I currently have 2 Excel tables: one table (Table 1) with the MRN (medical record number) and DC_DATE (discharge date from the hospital), and another table (Table 2) with MRN, OP_DATE (date of the operation), and OP_TIME (time spent in operating room). My end goal is a table with MRN, DC_DATE, OP_DATE, and OP_TIME.

Problem 1: I can't just link the two via MRN because the patient could have had more than one operation within the specified time period, so they could have more than one entry in Table 2. In order to guarantee that I am matching the correct entries, I also need to link DC_DATE from Table 1 with OP_DATE from Table 2.

Problem 2: DC_DATE and OP_DATE may not be the same day because the patient may have had the operation and stayed in the hospital for another couple days.


Question: Is it possible to link two tables with approximate matches? For example, can I link MRN and DC_DATE from Table 1 to the MRN and closest OP_DATE (relative to DC_DATE) from table 2? Alternatively, can I set a parameter that links the DC_DATE with an OP_DATE that falls within a range, e.g. within 7 days prior to the DC_DATE?

I really appreciate your help! Also, if anyone has an alternative method that might work, I'm all ears. I just don't want to spend countless hours going through and manually looking up these numbers when I'm already going to have to be looking up a ton of other information from the actual patient charts.

[CJ_London]

short answer is yes in principle (using a mixture of linking and criteria) however you need to clarify closest - is that closest before? or after? or just nearest to either way?

[AnAverageDave]

I appreciate your help Ajax!

It would be closest before, as the patient can't get discharged until after their procedure. Thus, OP_DATE is always before DC_DATE. How would I appropriately list the criteria?

[CJ_London]

something like this

SELECT *
FROM Table1 inner join table2
ON table1.mrn=table2.mrn
WHERE table2.OPDate = (SELECT Max(OPDate) FROM Table2 as T WHERE mrn=Table1.mrn AND OPDate<=DCDate)

[ssanfu]

@Ajax
I don't know, since I haven't worked with sub queries much, but isn't there a "T" missing in the sub query?

WHERE table2.OPDate = (SELECT Max(OPDate) FROM Table2 as T WHERE T.mrn = Table1.mrn AND OPDate<=DCDate)

I came up with a different query

SELECT T1.MRN, T2.OP_Date, T1.DC_Date
FROM Table1 AS T1 INNER JOIN Table2 AS T2 ON T1.MRN = T2.MRN
WHERE T1.DC_Date Between T2.OP_Date And T2.OP_Date+7;

but I think yours is better because I hard coded dc_date within op_date + 7 days.

[CJ_London]

but isn't there a "T" missing in the sub query?

Not required since T is the default

same as

SELECT afield from atable

you don't need to say

SELECT atable.afield from atable

[ssanfu]

Thanks, Ajax. Learned something new about aliases.

[orange]

I'd like to hear more about MRN. It seems that the MRN on the 2 tables should be consistent. Do you have a working definition/description of MRN?

Perhaps some sample data would clarify the situation.