Find the latest admission or attendance for a patient

SQL

All the data in this article is fake data generated artificially.

Here are two ways to find only the most recent activity for a patient. The examples are based on inpatient discharges, but these can easily be adapted for outpatient attendances or other activity with timestamps.

The following table shows a made-up patient with six inpatient admissions. Our goal is to write a query which returns only the most recent admission, in this case with spell number 201.

Table 1: 6 records
PatientID Spell Number Discharge Date
2 1206 2019-10-03
2 1005 2019-10-08
2 804 2019-10-20
2 603 2020-03-11
2 402 2020-05-20
2 201 2020-09-01

Method 1: Self-join

In the query below, I’ve joined the spell table back onto itself, with the condition that the joined record must have a later discharge date. This creates an output where there is a row for every combination of a spell with a later spell for that same patient.

SELECT ips.LocalPatientIdentifier AS 'PatientID'
        ,ips.HospitalProviderSpellNumber AS 'Spell Number'
        ,ips.DischargeDate AS 'Discharge Date'
        ,ips2.HospitalProviderSpellNumber AS 'Spell Number 2'
        ,ips2.DischargeDate AS 'Discharge Date 2'

FROM spell ips
LEFT JOIN spell ips2
  ON ips.LocalPatientIdentifier = ips2.LocalPatientIdentifier
  AND ips.DischargeDate < ips2.DischargeDate

WHERE ips.LocalPatientIdentifier = 2

ORDER BY ips.DischargeDate
          ,ips2.HospitalProviderSpellNumber
Table 2: 16 records
PatientID Spell Number Discharge Date Spell Number 2 Discharge Date 2
2 1206 2019-10-03 201 2020-09-01
2 1206 2019-10-03 402 2020-05-20
2 1206 2019-10-03 603 2020-03-11
2 1206 2019-10-03 804 2019-10-20
2 1206 2019-10-03 1005 2019-10-08
2 1005 2019-10-08 201 2020-09-01
2 1005 2019-10-08 402 2020-05-20
2 1005 2019-10-08 603 2020-03-11
2 1005 2019-10-08 804 2019-10-20
2 804 2019-10-20 201 2020-09-01
2 804 2019-10-20 402 2020-05-20
2 804 2019-10-20 603 2020-03-11
2 603 2020-03-11 201 2020-09-01
2 603 2020-03-11 402 2020-05-20
2 402 2020-05-20 201 2020-09-01
2 201 2020-09-01 NA NA

In the table above, we see that the most recent spell, with spell number 201, is the only one for which ips2.HospitalProviderSpellNumber is null. This is because it is the only spell for which there is no spell with a greater discharge date in the spells table. Therefore we can filter on ips2.HospitalProviderSpellNumber to return only the most recent discharge.

SELECT ips.LocalPatientIdentifier
        ,ips.HospitalProviderSpellNumber
        ,ips.DischargeDate

FROM spell ips
LEFT JOIN spell ips2
  ON ips.LocalPatientIdentifier = ips2.LocalPatientIdentifier
  AND ips.DischargeDate < ips2.DischargeDate

WHERE ips.LocalPatientIdentifier = 2
AND ips2.HospitalProviderSpellNumber IS NULL
Table 3: 1 records
LocalPatientIdentifier HospitalProviderSpellNumber DischargeDate
2 201 2020-09-01

This method can be used to look for the earliest spell by exchanging the less than for a greater than in the join condition. Be careful of the situation in which you may have activity taking place on exactly the same date. In this case, you might want to include the time of the activity in the join too.

Method 2: Correlated subquery

In this approach, we instead write a correlated sub-query to search for the latest spell date for each patient.


SELECT ips.LocalPatientIdentifier
        ,ips.HospitalProviderSpellNumber
        ,ips.DischargeDate

FROM spell ips

WHERE ips.DischargeDate = (SELECT MAX(ips2.DischargeDate)
                          FROM spell ips2
                          WHERE ips2.LocalPatientIdentifier = ips.LocalPatientIdentifier)
AND ips.LocalPatientIdentifier = 2
Table 4: 1 records
LocalPatientIdentifier HospitalProviderSpellNumber DischargeDate
2 201 2020-09-01

Arguably this is a more readable query. Again, this can be used to look for the earliest spell, by switching the MAX to MIN, and again, you need to be careful about the situation in which two spells have exactly the same discharge date.