Top 2 From Multiple Person IDs

[GregD]

I have a table of patients (one) and a table of vital statistics (many). I need to get a list of the patients whose values of the last 2 readings in the year are over a certain value. Both of the last 2 need to be over a cretain threshold for the patient to be counted.

I can group on Patient_ID and count the number of values that are over the threshold, but that does not really account for only the last 2 values. It could be the 1st and the 3rd or the 7th and 15th.

SELECT tblAllHTNPatients.PatientID, Count(tblVital.[Value ]) AS HighBP
FROM tblAllHTNPatients INNER JOIN tblVital ON tblAllHTNPatients.PatientID = tblVital.PatientID
WHERE (((tblVital.[Value ])>160) AND ((tblVital.EncounterDate)>=#1/1/2010#) AND ((tblVital.ItemName)="Systolic Blood Pressure"))
GROUP BY tblAllHTNPatients.PatientID
HAVING (((Count(tblVital.[Value ]))>1));

The query below will show me those patients whose last value was over 160, but not the last 2. It gets me half way there.

SELECT Q.PatientID INTO tblHTNReportPatients
FROM tblVital INNER JOIN (

SELECT tblAllHTNPatients.PatientID, tblVital.ItemName, 
Max(tblVital.EncounterDate) AS MaxEncDate
FROM tblAllHTNPatients INNER JOIN tblVital 
ON tblAllHTNPatients.PatientID = tblVital.PatientID
WHERE (((tblVital.EncounterDate)>=#1/1/2010#) AND ((tblVital.ItemName)="Systolic Blood Pressure"))
GROUP BY tblAllHTNPatients.PatientID, tblVital.ItemName
) AS Q 

ON (tblVital.EncounterDate = Q.MaxEncDate) AND 
(tblVital.ItemName = Q.ItemName) AND 
(tblVital.PatientID = Q.PatientID)
WHERE (((tblVital.[Value ])>160));

What I've been doing to running multiple queries - one for each patient - that gets the TOP 2 Values ORDER BY EncounterDate DESC and put those in to a table. I then have the top 2 for each patient and I then group and count of patient ID where count > 1 and it works.

The problem is, this takes for ever. It is only 600 patients, but running a TOP 2 query for each and insert it in to a table takes 30 or 40 minutes.

What I would like is a single query that returns the top to values for all patients that wouldn't take for ever to run. I could then count where over 1 and get my cohort of patients.

Is this possible and can anyone point me in the right direction.

Greg

 

[Galaxiom]

I haven't had a chance to look at the details of the problem but you should address the following issues.

Have you got indexes on the PatientID, EncounterDate and Value fields in tblVital? Indexes make a huge difference to speed. (BTW, Value is a reserved word and should be avoided.)

You should also encode the ItemName values as integers and record their text in a lookup table. Comparing long strings is very slow.

The Grouping subquery should be done on tblVital.PatientID alone. The join with tblAllHTNPatients is superfluous.

 

[GregD]

I haven't had a chance to look at the details of the problem but you should address the following issues.

Have you got indexes on the PatientID, EncounterDate and Value fields in tblVital? Indexes make a huge difference to speed. (BTW, Value is a reserved word and should be avoided.)

You should also encode the ItemName values as integers and record their text in a lookup table. Comparing long strings is very slow.

The Grouping subquery should be done on tblVital.PatientID alone. The join with tblAllHTNPatients is superfluous.

All 100% true. Unfortunately, this is not a database I designed, nor can I make changes to it. I have to work with what I have.

Greg

 

[Galaxiom]

Hmmm. Working with one hand tied behind your back makes it difficult.

You can at least remove the join from the subquery. Surely they can let you can add indexes to the fields since there is no chance of that breaking anything. The queries are so slow it does strongly suggest these fields are not indexed.

 

[GregD]

OK, well, I don't see how I can remove the join from the sub query. If I remove the join there is nothing to relate the patients to their vitals and I would return zillions of rows. I could simply remove the table of patients but that would return thousands of more MaxEncDates than I need. The table tblAllHTNPatients only has 650 rows. tblVitals has vitals for 20000+ patients. I could (should) remove tblVital.ItemName from the subquery, but that does not really matter at this point. That query, with the subquery in it, runs in a matter of seconds. It is not the issue.

Yes, adding indexes can be done, but that doesn't answer the question of this thread: Is there a way to return the top 2 on a 1 to many join between two tables in 1 query. Right now I am doing multiple queries - one for each row in tblAllHTNPatients. This is in a loop in code in a module. Each iteration of the loop grabs the top two for the patient and inserts those rows in to another table.

With or without indexing, these seems to be an inefficient way of doing things. Maybe that is the only way to do it. I don't know. If I did know, I guess I wouldn't have posted the question.

 

[Galaxiom]

OK, well, I don't see how I can remove the join from the sub query. If I remove the join there is nothing to relate the patients to their vitals and I would return zillions of rows.

All the fields used in that subquery exist in tblVital. Indeed there is no reason to include tblAllHTNPatients anywhere in the whole INSERT query.

 

[GregD]

With all do respect, if you don't know how to help me with the question that I asked in the first place, then why are you wasting your time with this thread. Whether the join is needed or not need is COMPLETELY IRRELEVANT. I'm not using that query for anything. It does not give me what I need. It is something I whipped up to aid in a explanation of the problem.

So, yes, Kudos to you for pointing out a minor flaw in a query that has no purpose.

Now, back to the real problem....

 

[Galaxiom]

The concept goes something like this.

Select the maximum encounter date (LastDate) from tblVital records grouped by Patient having ItemName = "Systolic Blood Pressure".

Join the records to a query on an alias of tblVital where ItemName = "Systolic Blood Pressure" and select the maximum date of records having the date not the same as the LastDate (PenultimateDate).

This should provide one record for each patient that has a least two records for SBP plus a field for each of the last two encounter dates.

Join this back to two aliases of tblVital on Patient and each of these two dates to get the SBP readings for those dates for each Patient.

The following query is complex and completely untested. I would be incredibly surprised if it works without considerable troubleshooting, particularly as it is now well after midnight.

Break it down into seperate queries and use them as the base for more queries if it gets too overwhelming. Hopefully it is close enough to see the end of the tunnel and the concept will get you started.

SELECT PatientID, C.LastDate, X.[Value ] As LastValue, C.PenultimateDate, Y.[Value ] AS PenultimateValue
FROM
(
  (SELECT PatientID, LastDate, MAX(OtherDate) As PenultimateDate
    (
     SELECT PatientID, MAX(EncounterDate) AS LastDate
     FROM tblVital AS A
     GROUP BY PatientID
     HAVING ItemName="Systolic Blood Pressure"
    )
   INNER JOIN
    (
     SELECT PatientID, EncounterDate AS OtherDate
     FROM tblVital AS B
     GROUP BY PatientID
     WHERE ItemName="Systolic Blood Pressure"
    )
   ON A.PatientID = B.PatientID
   GROUP BY PatientID, LastDate
   WHERE B.EncounterDate <> A.LastDate
  ) AS C
  (
  INNER JOIN tblVital As X
  ON C.PatientID = X.PatientID
  AND C.LastDate = X.EncounterDate
  )
  INNER JOIN tblVital As Y
  ON C.PatientID = Y.PatientID
  AND C.PenultimateDate = Y.EncounterDate
) AS Z
WHERE X.[Value ] > 160 AND Y.[Value ] > 160
;

Sorry for not addressing your actual question in earlier posts. As I said I hadn't really looked at the question properly and was offering advice on the code I saw. Then I answered the comment you made about removing the table from the query in your reply.

As you can see this query is quite complex. I initially thought there would be another way to use TOP 2 in subqueries but I can't see it. The fact that you have already explored that path and had to use a loop suggests maybe that is really a dead end afterall.

Good luck troubleshooting. I am confident the concept is sound, but the code......... ????