Querying 3 consecutive months (possible?) (1 Viewer)

[jimjaix]

Hi

I have a very complicated situation (as least to me). I have a table that contains Provider ID, Patient ID and Date of Visits. I want to know if it's possible to create a query that will produce the output I want.

I want to know from all the providers I have, which one (will be multiple providers) have 5 or more unique patients AND with date of visits within 3 consecutive months. For e.g., Dr. A has 8 patients and 6 of those patients visits within 3 consecutive months (3 months back to back e.g March, April, May). So my query will return this Dr. as one of the results. Is it possible?




I attached a dummy database for clarification

The result should be PROVIDER_ID 123456789 because that provider has 5 different patients with 3 consercutive months visit.

I also would like to drill down the patient level which should result the following...

Provider_id patient_id
123456789 22223333
123456789 33334444
123456789 44443333
123456789 88664422
123456789 11112222

So there's a couple criteria I am look it at, first the Provider needs to have 5 unique individuals or more and within that 5 individuals or more they need to have 3 consecutive date of visits if all those are met they I should have the provider ID.

Thank you so much!

 

[GanzPopp]

For your first problem, the following query works (unfortunately you do not have too many examples in your database, so it's hard to check the correctness). I'm pretty sure there might be a better solution.

SELECT D.PROVIDER_ID 
FROM (
    SELECT C.PROVIDER_ID, C.PATIENT_ID 
    FROM (
        SELECT A.PROVIDER_ID, A.PATIENT_ID
        FROM VISIT_VOLUMN AS A, VISIT_VOLUMN AS B
        WHERE A.PROVIDER_ID=B.PROVIDER_ID AND A.PATIENT_ID=B.PATIENT_ID AND A.VISIT_DATE<B.VISIT_DATE 
            AND DateDiff("m",A.VISIT_DATE,B.VISIT_DATE) BETWEEN 0 AND 2 
        ORDER BY A.PATIENT_ID, A.VISIT_DATE
    ) AS C 
    GROUP BY C.PROVIDER_ID, C.PATIENT_ID
) AS D 
GROUP BY D.PROVIDER_ID HAVING COUNT(1)>=5

Of course, the ordering in the subsubquery can be left out.

Your second problem is easily solved by extracting the first subquery.