CODEWITHSUNDEEP
sqlsql-servert-sql
adyavanapalli
adyavanapalli

Reputation: 500

T-SQL - Select patients who are readmitted (within some duration) with the same diagnosis

I have a table with the following schema:

CREATE TABLE Codes
(
    diagnosis_code CHAR,
    visit_date     DATE,
    visit_id       INT,
    patient_id     int
);

I would like to output the patient_ids where the patient is readmitted (so a different visit_id) with the same diagnosis_code within a certain time (say 15 days). For example, if I have the following entries in the table:

diagnosis_code visit_date visit_id    patient_id
-------------- ---------- ----------- -----------
A              2018-01-01 1           1
B              2018-01-01 1           1
A              2018-01-07 2           1
C              2018-01-01 3           2
D              2018-01-01 4           3
D              2018-01-20 5           3
E              2018-01-01 6           4
E              2018-01-01 6           4
A              2018-01-07 7           1

The query would return only patient_id = 1, and the rationales are as follows:

  1. 1, because between visit_id 1 and 2, this patient shared diagnosis code A.
  2. Not 2 because this patient was only admitted once.
  3. Not 3 because this patient, although readmitted for the same diagnosis, was not readmitted within 15 days of their initial visit.
  4. Not 4 because this patient has a duplicated diagnosis code in the same visit.
  5. Notice that patient_id = 1 is readmitted for the same diagnosis during visit_id = 7, but he was already counted once before.

Upvotes: 0

Views: 457

Answers (3)

GGadde
GGadde

Reputation: 391

You can also use inbuilt FIRST_VALUE and DATEADD functions to achieve this:

SELECT 
    DISTINCT patient_id,diagnosis_code 
FROM
(SELECT 
    FIRST_VALUE(visit_date) OVER (PARTITION BY patient_id,diagnosis_code ORDER BY visit_id ASC) AS Initial_Visit,
    DATEADD(DAY,15,first_value(visit_date) OVER (PARTITION BY patient_id,diagnosis_code ORDER BY visit_id ASC)) Window
    ,* FROM Codes
)m
 WHERE 
    Initial_Visit <> visit_date
    AND visit_date <= Window

Upvotes: 0

KeithL
KeithL

Reputation: 5594

i used lag.

declare @Codes table
(
    diagnosis_code CHAR,
    visit_date     DATE,
    visit_id       INT,
    patient_id     int
);

insert into @Codes
values
('A',              '2018-01-01' ,1,           1)
,('B' ,             '2018-01-01', 1,           1)
,('A' ,             '2018-01-07', 2,           1)
,('C'             ,'2018-01-01', 3,           2)
/*
D              2018-01-01 4           3
D              2018-01-15 5           3
E              2018-01-01 6           4
E              2018-01-01 6           4
A              2018-01-07 7           1
*/



select *
from (
select *
    --,rn=row_number() over (partition by patient_ID,diagnosis_code order by visit_date)
    ,DaysSince = datediff(day,lag(visit_date,1) over (partition by patient_ID,diagnosis_code order by visit_date),visit_date) 
from @Codes
) a
where a.DaysSince<=15

Upvotes: 0

The Impaler
The Impaler

Reputation: 48770

You could try a simple join, adding the conditions you described:

select
    distinct c.patient_id
  from codes c
  join codes d on d.patient_id = c.patient_id
              and d.visit_id <> c.visit_id
              and d.diagnosis_code = c.diagnosis_code
              and d.visit_date between c.visit_date
                  and dateadd(day, 15, c.visit_date)

Upvotes: 1

Related Questions