CODEWITHSUNDEEP
sqlpostgresql
Py.rookie89
Py.rookie89

Reputation: 129

Find patients that develop a more severe disease

I have a table patient_details that has id, diagnosis_date and diagnosis_code. A unique ID can have multiple entries meaning they were diagnosed with different diseases at different times.

GOAL: I want to see patients that eventually progress to having disease code 5.10. So I want to see patients who were first diagnosed with code 5 and then progress to diagnosis 5.10. I am not sure how to isolate the dates for each unique patient and see who went from an initial diagnosis of 5 to eventually 5.10. I ultimately just need the count of patients who go from diagnosis code 5 to 5.10

Example of table:

ID    |diagnosis_date|diagnosis_code
PT2073|2015-02-28    |5
PT2073|2019-02-28    |5.10
PT2013|2015-04-28    |1
PT2013|2017-02-11    |5
PT2013|2017-07-11    |5.10

Upvotes: 1

Views: 208

Answers (1)

GMB
GMB

Reputation: 222432

This might do the trick:

select id
from patient_details
group by id
having
    min(case when diagnosis_code = 5 then diagnosis_date end)
    < max(case when diagnosis_code = 5.1 then diagnosis_date end)

This will ensure that:

  • the patient has at least one record with diagnosis_code = 5 and another with diagnosis_code = 10

  • the date they were first diagnosed with code 5 is less than the date they were last diagnosed 5.1

Demo on DB Fiddle

Sample data:

id     | diagnosis_date | diagnosis_code
:----- | :------------- | -------------:
PT2073 | 2015-02-28     |           4.00
PT2073 | 2019-02-28     |           5.10
PT2013 | 2015-04-28     |           1.00
PT2013 | 2017-02-11     |           5.00
PT2013 | 2017-07-11     |           5.10

Results:

| id     |
| :----- |
| PT2013 |

Upvotes: 1

Related Questions