CODEWITHSUNDEEP
sqlsql-server-2008self-join
Lauren
Lauren

Reputation: 45

Link subsequent patient visits from same table in SQL

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!

Upvotes: 3

Views: 508

Answers (3)

TT.
TT.

Reputation: 16137

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;

Upvotes: 1

Andomar
Andomar

Reputation: 238098

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.

Upvotes: 2

Talha Ahmed Khan
Talha Ahmed Khan

Reputation: 15433

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.

Upvotes: 1

Related Questions