Get lab values closest to a date

[kballing]

I'm pulling lab values from a hospital DB. They are stored in a table like this.

Table: LABS
Columns: LAB_ID, PATIENT_ID, LAB_TYPE, DATE, RESULT, UNITS
Example: 123456, 654321, "WBC", 1/1/2000, 10.2, "K/uL"

I have a list of PATIENT_IDs and certain LAB_TYPES from certain dates that I want to look at.

Ultimately I want to compile this into a table like this:

Table: Results
Columns: PATIENT_ID, DATE, WBC, WBC_UNITS, RBC, RBC_UNITS, HCT, HCT_UNITS, HGB, HGB_UNITS
Example: 123456, 1/1/2000, 10.2, "K/uL", 4.8, "M/uL", 45.2, "%", 15.9, "g/dL"

I want to group results by patient and get the once closest to a specified date. I.E. If a patient had multiple WBC tests done, I only want the result from the one closest to a specific date. If RBC was tested on different days than WBC, give me the result closest to the specified date.

 

[Brianwarnock]

You can create a field using abs(datediff("d",patientdate,checkdate)) and select the min

brian

 

[CraigDolphin]

The general approach I'd take would be to create a calculated field that determined the absolute difference in days between the lab_date and the target date. Then sort the results in ascending order of the calculated field and select the result from the first record using either Dlookup, or using the Top 1 SQL argument.

Brian beat me to it. Damn thos eenglish speed typists!

 

[kballing]

I'll take a stab at answering my own post. I know how to separate different lab types using an if statement (actually it's an Oracle DB so it's a Decode statement). This gives me a table with all the columns that I need, but there are multiple records for the same patient and each lab result is in a separate record.

e.g. PAT_ID, lab1, units1, date1, lab2, units2, date2, lab3, units3, date3, etc.
123456, 2, 'mg', 1/2/2000, null, null, null, null, null, null, null, etc.
123456, null, null, null, 4, 'g/L', 1/1/2000, null, null, null, null, etc.
123456, null, null, null, null, null, null, 10.4, '%', 1/1/2000, null, etc.
123456, 3, 'mg', 1/4/2000, null, null, null, null, null, null, null, etc.
234567, null, null, null, 3, 'g/L', 12/30/1999, null, null, null, null, etc.
234567, 5, 'mg', 1/1/2000, null, null, null, null, null, null, null, etc.
etc.

To get dates closest to specified date I would want the min(abs(date1-date2)) but how to I return the results associated with that date if I group by PATIENT_ID?

Should I narrow the results down by date before separating by lab types?

 

[Brianwarnock]

You need 2 queries
The first gets the closest date as stated, and the Group you want it to relate to ie Patient Id (or patientId and lab if that is how you want it), then this query is joined on all its fields back to the original data to pull the rest of the data required.

I think that you had worked this out according to your response.

Brian

 

[kballing]

Brian, Craig, thanks for the quick replies. What you've said makes sense, but I think the underlying problem is that I need a result from each type of lab, however the dates for each lab are not related.
Your solutions would work if I were to run a query for each type of lab and then compile the results later. This could be tough since there are 25+ lab results that I need to get, then I've got to do the same for vital signs and other procedures.
I guess I could automate things with some VBA.

 

[Brianwarnock]

I think our last posts crossed, as you can see the min is the min per group, so just choose your grouping.

Brian

 

[kballing]

I'll mess around with things for a while, but I may have questions later so please check back in a while.