hd_labs carries both a patient_id and an encounter_id. Which one you join on decides what the result means:
SELECT l.test_name, l.result_value, e.department
FROM hd_labs l
JOIN hd_encounters e ON e.encounter_id = l.encounter_id
WHERE e.encounter_type = 'inpatient';
- Join on encounter_id to get the visit or stay the result was drawn in.
- Joining on patient_id matches each result with every encounter of that patient. A potassium drawn in March sits next to a clinic visit in May, rows multiply, and averages mix in stays the result has nothing to do with.
- A result is out of range when it is below
ref_low or above ref_high. Test both ends: sodium and potassium run low as often as high. Put brackets around the OR when other conditions sit beside it.
- Average one test at a time. An average across tests mixes mg/dL with mmol/L and means nothing.
Results outside the range, at either end, during inpatient stays.