[SQL] SQL Query to Retrieve Latest Values from Multiple Columns

I am working with a dataset that contains information about patients receiving care, and I'm trying to identify those eligible for viral load (VL) testing based on several criteria. I need to incorporate logic for selecting the latest encounter and the most recent viral load sample, ensuring that I handle cases where the sample dates may be null.

Here’s an overview of my eligibility criteria:

Adults (> 19 years) who have been on ART for more than 6 months, are not breastfeeding, and have a suppressed viral load (< 1000 copies/ml) should be scheduled for VL testing every 6 to 12 months.

Children and adolescents (≤ 18 years) are eligible for VL testing 6 months after their last sample is collected.

Pregnant women newly enrolled on ART are due for VL testing every 3 months until they are no longer in PMTCT.

Pregnant women already on ART are eligible for VL testing immediately upon confirming their pregnancy.

After a Third EAC Session, patients are eligible for VL testing in the following month.

I have created a query using Common Table Expressions (CTEs) to find the latest encounters and latest sample collection dates, but I am struggling to combine this with the eligibility criteria effectively. My query currently does the following:

Identifies the latest encounter for each client. Retrieves the latest viral load sample while handling null values. Applies the eligibility criteria. However, I'm unsure how to ensure the query properly considers both the latest encounter and the latest non-null viral load sample when determining eligibility.

I would appreciate any guidance on how to refine my SQL query to accurately achieve this, including suggestions for using the latest filled sample date for subsequent non null sample dates and ensuring the correct eligibility evaluation. Thank you!

This query is a high level of the data i have :

SELECT
fp.client_id,
mp.person_name_long,
mp.age,
fp.viral_load_value,
fp.date_vl_sample_collected,
TIMESTAMPDIFF(MONTH, fp.date_vl_sample_collected, "2024-10-01") AS months_since_last_vl,
CASE
WHEN mp.age > 19
AND fp.client_breastfeeding != 'Yes'
AND TIMESTAMPDIFF(MONTH, fh.art_start_date, NOW()) >= 6
AND fp.viral_load_value < 1000
AND TIMESTAMPDIFF(MONTH, fp.date_vl_sample_collected, "2024-09-24") BETWEEN 6 AND 12
THEN true
WHEN mp.age < 18
AND TIMESTAMPDIFF(MONTH, fp.date_vl_sample_collected, "2024-09-24") >= 6
THEN true
WHEN fp.client_pregnant = "Yes"
AND fh.art_start_date IS NOT NULL
AND TIMESTAMPDIFF(MONTH, fh.art_start_date, "2024-09-24") < 6
THEN true
WHEN fp.client_pregnant = "Yes"
AND TIMESTAMPDIFF(MONTH, fh.art_start_date, "2024-09-24") > 6
AND TIMESTAMPDIFF(MONTH, fh.art_start_date, NOW()) >= 3
THEN true
WHEN hvl.eac_session = "Third EAC Session"
AND TIMESTAMPDIFF(MONTH, hvl.encounter_datetime, NOW()) >= 1
THEN true
ELSE null
END AS eligible_for_vl,
fp.encounter_datetime,
fp.client_breastfeeding,
fp.client_pregnant,
fp.edd,
fh.art_start_date,
fp.viral_load_value,
fp.vl_results,
hvl.eac_session
FROM
ssemr_etl.ssemr_flat_encounter_hiv_care_follow_up AS fp
LEFT JOIN
ssemr_etl.mamba_dim_person mp ON mp.person_id = fp.client_id
LEFT JOIN
ssemr_etl.ssemr_flat_encounter_personal_family_tx_history fh ON fh.client_id = fp.client_id
LEFT JOIN
ssemr_etl.ssemr_flat_encounter_high_viral_load hvl ON hvl.client_id = fp.client_id
GROUP BY
fp.client_id,
mp.person_name_long,
mp.age,
fp.client_breastfeeding,
fp.viral_load_value,
fp.date_vl_sample_collected,
fp.edd,
fh.art_start_date,
fp.encounter_datetime,
fp.vl_results,
fp.client_pregnant,
hvl.eac_session,
hvl.encounter_datetime
ORDER BY
fp.client_id,
fp.encounter_datetime ASC;


The output is this, which is how the data looks like :

61 Stella Sunday 34 NULL NULL NULL 1 2024-07-05 08:40:06 No Yes 2014-03-03 2014-01-09 NULL NULL NULL
61 Stella Sunday 34 NULL 2022-03-24 30 NULL 2024-07-08 07:17:49 No No NULL 2014-01-09 NULL Below Detectable (BDL) NULL
62 Rahama Ambrose 34 NULL NULL NULL NULL 2024-07-05 11:16:33 NULL NULL NULL 2015-01-07 NULL NULL NULL
62 Rahama Ambrose 34 NULL 2024-03-15 6 NULL 2024-07-08 07:40:35 No No NULL 2015-01-07 NULL Below Detectable (BDL) NULL
63 Achoul Makuoch 40 NULL NULL NULL NULL 2024-07-05 09:17:16 No No NULL 2015-11-30 NULL NULL NULL
63 Achoul Makuoch 40 NULL NULL NULL NULL 2024-07-05 09:33:37 Yes No NULL 2015-11-30 NULL NULL NULL
63 Achoul Makuoch 40 NULL 2023-08-11 13 NULL 2024-07-05 09:42:02 No No NULL 2015-11-30 NULL Below Detectable (BDL) NULL
63 Achoul Makuoch 40 NULL 2024-09-06 0 NULL 2024-09-06 11:58:37 No No NULL 2015-11-30 NULL NULL NULL
64 Mary Juru 38 NULL NULL NULL 1 2024-07-05 11:12:06 No Yes NULL 2016-04-06 NULL NULL NULL
64 Mary Juru 38 NULL NULL NULL NULL 2024-07-05 11:31:10 No No NULL 2016-04-06 NULL NULL NULL
64 Mary Juru 38 NULL NULL NULL NULL 2024-07-08 08:09:51 NULL NULL NULL 2016-04-06 NULL NULL NULL
64 Mary Juru 38 NULL NULL NULL NULL 2024-07-08 08:31:26 NULL NULL NULL 2016-04-06 NULL NULL NULL
65 Aida James 33 NULL NULL NULL NULL 2024-07-05 12:09:33 No NULL NULL 2016-05-05 NULL NULL First EAC Session
65 Aida James 33 NULL NULL NULL NULL 2024-07-08 07:57:08 No No NULL 2016-05-05 NULL NULL First EAC Session
66 Faith Vihenda Malasi 29 NULL NULL NULL NULL 2024-07-05 12:14:33 No No NULL 2018-07-12 NULL NULL NULL
66 Faith Vihenda Malasi 29 NULL NULL NULL NULL 2024-07-08 07:30:37 No No NULL 2018-07-12 NULL NULL NULL
66 Faith Vihenda Malasi 29 NULL 2020-04-02 53 NULL 2024-07-08 07:43:35 No No NULL 2018-07-12 NULL Below Detectable (BDL) NULL
66 Faith Vihenda Malasi 29 NULL 2024-05-09 4 NULL 2024-07-08 07:59:06 No No NULL 2018-07-12 NULL Below Detectable (BDL) NULL


My query that is not as accurate looks like this, and does not consider the latest date_vl_sample_collected for subsequent null date_vl_sample_collected :

WITH LatestEncounters AS (
-- Select the latest encounter for each client
SELECT
fp.client_id,
MAX(fp.encounter_datetime) AS latest_encounter
FROM ssemr_etl.ssemr_flat_encounter_hiv_care_follow_up fp
GROUP BY fp.client_id
),
LatestViralLoadSample AS (
-- Select the latest viral load sample for each client where a sample is available
SELECT
fp.client_id,
MAX(fp.date_vl_sample_collected) AS latest_vl_sample
FROM ssemr_etl.ssemr_flat_encounter_hiv_care_follow_up fp
WHERE fp.date_vl_sample_collected IS NOT NULL
GROUP BY fp.client_id
),
SubsequentSamples AS (
-- Check if there are any subsequent samples after the latest viral load sample for each client
SELECT
fp.client_id,
COUNT(fp.date_vl_sample_collected) AS subsequent_sample_count
FROM ssemr_etl.ssemr_flat_encounter_hiv_care_follow_up fp
JOIN LatestViralLoadSample lvs ON fp.client_id = lvs.client_id
WHERE fp.date_vl_sample_collected > lvs.latest_vl_sample
GROUP BY fp.client_id
)
SELECT
fp.client_id,
mp.person_name_long,
mp.age,
lvs.latest_vl_sample AS date_vl_sample_collected,
TIMESTAMPDIFF(MONTH, lvs.latest_vl_sample, CURDATE()) AS months_since_last_vl,
le.latest_encounter AS encounter_datetime,
fp.client_breastfeeding,
fp.client_pregnant,
fp.edd,
fh.art_start_date,
fp.viral_load_value,
fp.vl_results,
hvl.eac_session,
CASE
-- Eligibility logic based on age, breastfeeding status, ART start date, viral load, etc.
WHEN mp.age > 19
AND fp.client_breastfeeding != 'Yes'
AND TIMESTAMPDIFF(MONTH, fh.art_start_date, CURDATE()) >= 6
AND fp.viral_load_value < 1000
AND TIMESTAMPDIFF(MONTH, lvs.latest_vl_sample, CURDATE()) BETWEEN 6 AND 12
AND (ss.subsequent_sample_count = 0 OR ss.subsequent_sample_count IS NULL)
THEN 1
WHEN mp.age < 18
AND TIMESTAMPDIFF(MONTH, lvs.latest_vl_sample, CURDATE()) >= 6
AND (ss.subsequent_sample_count = 0 OR ss.subsequent_sample_count IS NULL)
THEN 1
WHEN fp.client_pregnant = 'Yes'
AND TIMESTAMPDIFF(MONTH, fh.art_start_date, CURDATE()) < 6
THEN 1
WHEN fp.client_pregnant = 'Yes'
AND TIMESTAMPDIFF(MONTH, fh.art_start_date, CURDATE()) > 6
AND TIMESTAMPDIFF(MONTH, fh.art_start_date, CURDATE()) >= 3
THEN 1
WHEN hvl.eac_session = 'Third EAC Session'
AND TIMESTAMPDIFF(MONTH, hvl.encounter_datetime, CURDATE()) >= 1
THEN 1
ELSE 0
END AS eligible_for_vl
FROM ssemr_etl.ssemr_flat_encounter_hiv_care_follow_up AS fp
JOIN LatestEncounters le ON le.client_id = fp.client_id
LEFT JOIN LatestViralLoadSample lvs ON lvs.client_id = fp.client_id
LEFT JOIN SubsequentSamples ss ON ss.client_id = fp.client_id
LEFT JOIN ssemr_etl.mamba_dim_person mp ON mp.person_id = fp.client_id
LEFT JOIN ssemr_etl.ssemr_flat_encounter_personal_family_tx_history fh ON fh.client_id = fp.client_id
LEFT JOIN ssemr_etl.ssemr_flat_encounter_high_viral_load hvl ON hvl.client_id = fp.client_id
-- Group to ensure only one row per client
GROUP BY
fp.client_id,
mp.person_name_long,
mp.age,
lvs.latest_vl_sample,
fp.client_breastfeeding,
fp.viral_load_value,
fp.edd,
fh.art_start_date,
le.latest_encounter,
fp.vl_results,
fp.client_pregnant,
hvl.eac_session,
hvl.encounter_datetime
HAVING MAX(lvs.latest_vl_sample)
ORDER BY
fp.client_id ASC;

Continue reading...