Picking two most recent dates for each individual

Hello Experts,

I asked this question earlier, but I don't think I was very clear. I need pick two most recent dates ([MeasurementDt]) and weights ([PatWeight]) for each child. I want to subtract them to determine if there has been a weight change of 10 or more lbs in the 2 most recent months. Thank you for your help.

James
SELECT tblPatients.LName, tblNutritionHtWt.MeasurementDt, tblNutritionHtWt.PatWeight
FROM tblNutritionHtWt INNER JOIN tblPatients ON tblNutritionHtWt.PatID = tblPatients.PatID;

Open in new window

jdallainAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
calpurniaConnect With a Mentor Commented:
Hi, I hope you don't mind me butting in here - I looked at this question when it first came out, but didn't post anything at the time as other people were already on the case.

Your problem is an example of  the more general "find the top n items in each group" problem, which I've only recently got to grips with (and am consequently still quite excited about!)

They way to approach this problem is to ask "How many records have the same PatID and a later MeasurementDt than the current record?"

If you run the following query, you'll see that the most recent MeasurementDt for each patient has a DateCount of 0.

SELECT T1.PatID, T1.MeasurementDt, (SELECT Count(*) FROM tblNutritionHtWt AS T2 WHERE T1.PatID=T2.PatID AND T1.MeasurementDt<T2.MeasurementDt) AS DateCount
FROM tblNutritionHtWt AS T1;

So we can now pull out the most recent record for each patient:

SELECT T1.PatID, T1.MeasurementDt, T1.PatWeight
FROM tblNutritionHtWt AS T1
WHERE (SELECT Count(*) FROM tblNutritionHtWt AS T2 WHERE T1.PatID=T2.PatID AND T1.MeasurementDt<T2.MeasurementDt)=0;


If we wanted the 2 most recents records, we'd use:

SELECT T1.PatID, T1.MeasurementDt, T1.PatWeight
FROM tblNutritionHtWt AS T1
WHERE (SELECT Count(*) FROM tblNutritionHtWt AS T2 WHERE T1.PatID=T2.PatID AND T1.MeasurementDt<T2.MeasurementDt)<2;


We can then add in some subqueries to get the last-but-one MeasurementDt for each patient, and the corresponding weight. (This is starting to look a bit messy - I'd be interested to know if there's a neater way to do it...)

SELECT T1.PatID, T1.MeasurementDt, T1.PatWeight, (SELECT max(T3.MeasurementDt) FROM tblNutritionHtWt T3 WHERE T1.PatID=T3.PatID and T1.MeasurementDt<>T3.MeasurementDt) AS PrevDate, (SELECT T4.PatWeight FROM tblNutritionHtWt AS T4 WHERE T1.PatID=T4.PatID AND T4.MeasurementDt=(SELECT max(T3.MeasurementDt) FROM tblNutritionHtWt T3 WHERE T1.PatID=T3.PatID and T1.MeasurementDt<>T3.MeasurementDt)) AS PrevWeight
FROM tblNutritionHtWt AS T1
WHERE ((((SELECT Count(*) FROM tblNutritionHtWt AS T2 WHERE T1.PatID=T2.PatID AND T1.MeasurementDt<T2.MeasurementDt))=0));


A slightly different approach is to use GROUP BY and HAVING - this is the alternative version for the '2 most recent records' query above:

SELECT P.PatID, P.MeasurementDt, P.PatWeight
FROM tblNutritionHtWt AS P, [SELECT P1.PatID, P1.MeasurementDt
FROM tblNutritionHtWt AS P1
GROUP BY P1.PatID, P1.MeasurementDt
HAVING (SELECT Count(*) FROM tblNutritionHtWt P2 WHERE P1.PatID=P2.PatID AND P2.MeasurementDt>P1.MeasurementDt) < 2]. AS [%$##@_Alias]
WHERE P.PatID=P1.PatID AND P.MeasurementDt=P1.MeasurementDt;

Again, I'd be interested to know if there's any particular advantage of using one approach over the other.
0
 
puppydogbuddyCommented:
SELECT TOP 2 tblPatients.LName, tblNutritionHtWt.MeasurementDt, tblNutritionHtWt.PatWeight
FROM tblNutritionHtWt INNER JOIN tblPatients ON tblNutritionHtWt.PatID = tblPatients.PatID
ORDER BY tblNutritionHtWt.MeasurementDt DESC;
0
 
GRayLCommented:
Does this do it?

SELECTa.PatID, Max(a.MeasurementDt) As FirstDT, a.PatWeight, (SELECT Min(b.PatWeight) FROM tblNutritionHtWt AS b WHERE b.PatID=a.PatID and b.PatWeight > a.PatWeight) AS SecondWt FROM tblNutritionWt AS a GROUP BY a.PatID, (SELECT Min(b.PatWeight) FROM tblNutritionHtWt AS b WHERE b.PatID=a.PatID and b.PatWeight > a.PatWeight);
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
jdallainAuthor Commented:
Thanks puppydogbuddy. Like the name!  That gives me the two most recent dates out of everyone (just 2 dates). However, I need to get the 2 most recent dates for each child. There are about 90 kids, so 180 dates.
0
 
jdallainAuthor Commented:
Thanks GRayL! I'll give it a try.
0
 
jdallainAuthor Commented:
I got a...

Syntax error in query expression (SELECT Min(b.PatWeight) FROM tblNutritionHtWt AS b WHERE b.PatID=a.PatID and b.PatWeight > a.PatWeight.

Not exactly sure how to fix it.
SELECTa.PatID, Max(a.MeasurementDt) As FirstDT, a.PatWeight, (SELECT Min(b.PatWeight) FROM tblNutritionHtWt AS b WHERE b.PatID=a.PatID and b.PatWeight > a.PatWeight) AS SecondWt FROM tblNutritionWt AS a GROUP BY a.PatID, (SELECT Min(b.PatWeight) FROM tblNutritionHtWt AS b WHERE b.PatID=a.PatID and b.PatWeight > a.PatWeight);

Open in new window

0
 
GRayLCommented:
Check spaces and closing parentheses.
0
 
jdallainAuthor Commented:
ok
0
 
jdallainAuthor Commented:
Can I get back with you tomorrow GRayL? The new show Fringe is getting ready to start. It's pretty good.
0
 
jdallainAuthor Commented:
GRayL, I messed up. I thought I could figure this out on my own. I also started to ask this question again, but was told that I shouldn't do that. I was wondering if you could help me fix the syntax. I was told that you can't have a GROUP BY with a subquery. Thanks for all your help.
James
0
 
jdallainAuthor Commented:
It worked like a charm! Perfect, absolutely perfect! I can't thank you enough Calpurnia! I'll let you know if there is any difference.

Thanks again,
James
0
 
GRayLCommented:
idallain:  I suggest we open up the question and you re-assign the points to calpurnia.  I will take care of it.
0
 
jdallainAuthor Commented:
Thanks again! You're really good at this!
0
 
GRayLCommented:
You're welcome!
0
 
calpurniaCommented:
I'm glad I could help, James!

And thank you GRayL for your generosity in reassigning the points - I wasn't expecting that, and I truly appreciate it.
0
 
GRayLCommented:
Thanks, just giving credit where it's due.  I guess jdallain is mad at me because I misspelled his/her name a few posts ago ;-)  I thought it necessary because anyone reading this thread in the future would certainly be confused by the award were it to be left with my non-working query as the answer.
0
All Courses

From novice to tech pro — start learning today.