Link subsequent patient visits from same table in SQL

Question

I have a table containing records for patient admissions to a group of hospitals.

I would like to be able to link each record to the most recent previous record for each patient, if there is a previous record or return a null field if there is no previous record.

Further to this I would like to place some criteria of the linked records eg previous visit to the same hospital only, previous visit was less than 7 days before.

The data looks something like this (with a whole lots of other fields)

Record   PatientID hospital Admitdate DischargeDate 
1.       1.        A.       1/2/12.   3/2/12
2.       2.        A.       1/2/12.   4/2/12
3.       1.        B.       4/3/12.   4/3/12

My thinking was a self join but I can't figure out how to join to the record where the difference between the admit date and the patient's previous discharge date is the minimum.

Thanks!

Andomar · Accepted Answer · 2013-06-05T08:16:33.577

2

You could use row_number() to assign increasing numbers to records for each patient. Then you can left join to the previous record:

; with  numbered_records as
        (
        select  row_number() over (partition by PatientID, Hospital
                    order by Record desc) as rn
        ,       *
        from    YourTable
        )
select  *
from    numbered_records cur
left join
        numbered_records prev
on      prev.PatientID = cur.PatientID
        and prev.Hospital = cur.Hospital
        and prev.DischargeDate >= dateadd(day, -7, getdate())
        and prev.rn = cur.rn + 1

To select only the latest row per patient, add:

where   cur.rn = 1

at the end of the query.

edited Jun 05 '13 at 08:16

answered Jun 05 '13 at 05:05

Andomar

232,371
49
380
404

I will alert you to [this](https://connect.microsoft.com/SQLServer/feedback/details/377976/non-recursive-ctes-inefficiently-recompute-self-joins-and-row-number-instead-of-using-worktable) link to warn you that using WITH in this instance and for large datasets, may result in extremely poor performance. Better use a `#temp` table to materialize `numbered_records` first. – TT. Jun 05 '13 at 05:18
@Andomar Note that you sort only on record_id. If the user that inputs the data does so in the wrong order, your query will give incorrect results. Order on AdmitDate first then on Record, for fool-proof result. Gave the +1 already, but anyway – TT. Jun 05 '13 at 15:39

score 1 · Answer 2 · answered Jun 05 '13 at 05:06

It will give you the First 2 records of the same patients. If you want the same Hospital then add another check of Hospital with the PatientID. Also can add the Date as well.

SELECT * FROM T1 t
WHERE (2 >= (SELECT Count(*) FROM T1 tmp 
             WHERE t.PatientID = tmp.PatientID 
             AND t.Record <= tmp.Record))

It will only bring the one record if there is only one entry.

TT. · Answer 3 · 2013-06-06T06:17:40.393

Note that:

I used DATE for data type. It might be possible that a patient visits one hospital before noon, and another in the afternoon. You would use DATETIME in that case.

Sorting on the partitioning uses dt_admit before record_id, to allow for entry of data in any order.

CREATE TABLE #hdata(
  record_id INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
  patient_id INT NOT NULL,
  hospital_id INT NOT NULL,
  dt_admit DATE NOT NULL,
  dt_discharge DATE NULL
);
INSERT INTO #hdata(
  patient_id,
  hospital_id,
  dt_admit,
  dt_discharge
)
VALUES (
  1,
  1,
  '2012-02-01',
  '2012-02-03'
), (
  2,
  1,
  '2012-02-01',
  '2012-02-04'
), (
  1,
  2,
  '2012-03-04',
  '2012-03-04'
);

-- 1/ link each record to the previous record for each patient, NULL if none
SELECT
  record_id,
  patient_id,
  ROW_NUMBER() OVER (PARTITION BY patient_id ORDER BY dt_admit,record_id) AS visit_seq_id
INTO
  #visit_sequence
FROM
  #hdata;

SELECT
  v1.record_id,
  v1.patient_id,
  v2.record_id AS previous_record_id
FROM
  #visit_sequence AS v1
  LEFT JOIN #visit_sequence AS v2 ON
    v2.patient_id=v1.patient_id AND
    v2.visit_seq_id=v1.visit_seq_id-1
ORDER BY
  v1.record_id;

DROP TABLE #visit_sequence;

-- 2/ criteria on linked records: same hospital, previous visit < 7 days
SELECT
  record_id,
  patient_id,
  hospital_id,
  dt_admit,
  ROW_NUMBER() OVER (PARTITION BY patient_id,hospital_id ORDER BY dt_admit,record_id) AS visit_seq_id
INTO
  #visit_sequence_elab
FROM
  #hdata;

SELECT
  v1.record_id,
  v1.patient_id,
  v2.record_id AS previous_record_id
FROM
  #visit_sequence_elab AS v1
  LEFT JOIN #visit_sequence_elab AS v2 ON
    v2.patient_id=v1.patient_id AND
    v2.hospital_id=v1.hospital_id AND
    v2.visit_seq_id=v1.visit_seq_id-1 AND
    DATEDIFF(DAY,v1.dt_admit,v2.dt_admit)<7
ORDER BY
  v1.record_id;

DROP TABLE #visit_sequence_elab;

DROP TABLE #hdata;

Link subsequent patient visits from same table in SQL

3 Answers3