I want to find the patient id with disease id 1 in his nth visit and disease id 2 in his immediate n-CodePudding

I have a database visit containing VisitID, PatientID, DiseaseID and Date. In the screenshot below PatientID 58 should be present as it has DiseaseID 1 in its n-th visit and DiseaseID 2 in its immediate next n 1 visit:

But PatientID 92 should not be present as DiseaseID 2 is not present immediate next to DiseaseID 1 on the basis of date:

I tried this

SELECT DISTINCT PatientID
FROM Visit
WHERE DiseaseID = 1
  AND PatientID IN (SELECT PatientID FROM Visit WHERE DiseaseID = 2)

Using this I got all those PatientID which are having both 1 and 2 DiseaseID but I don't know how to find the PatientID with DiseaseID 1 in its n-th visit and DiseaseID 2 in its immediate next n 1 visit on the basis of date.

CodePudding user response：

There are several ways to do this depending on the DB system you are using. In standard SQL try something like this:

SELECT DISTINCT v1.patientid
FROM visit v1, visit v2
WHERE v1.patientid = v2.patientid
  AND v1.diseaseid = 1
  AND v2.diseaseid = 2
  AND v1.date < v2.date
  AND NOT EXISTS (
    SELECT 1 FROM visit v3
    WHERE v3.patientid = v1.patientid
      AND v1.date < v3.date
      AND v3.date < v2.date
  )

The idea is to join two queries, one for visits with disease 1 and the other for disease 2 by patient and check if there was no other visit of that patient in between.