asked on

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

puppydogbuddy

SELECT TOP 2 tblPatients.LName, tblNutritionHtWt.MeasurementDt, tblNutritionHtWt.PatWeight
FROM tblNutritionHtWt INNER JOIN tblPatients ON tblNutritionHtWt.PatID = tblPatients.PatID
ORDER BY tblNutritionHtWt.MeasurementDt DESC;

GRayL

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);

jdallain

ASKER

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.

jdallain

ASKER

Thanks GRayL! I'll give it a try.

jdallain

ASKER

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

GRayL

Check spaces and closing parentheses.

jdallain

ASKER

jdallain

ASKER

Can I get back with you tomorrow GRayL? The new show Fringe is getting ready to start. It's pretty good.

jdallain

ASKER

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

ASKER CERTIFIED SOLUTION

calpurnia

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jdallain

ASKER

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

GRayL

idallain: I suggest we open up the question and you re-assign the points to calpurnia. I will take care of it.

GRayL

https://www.experts-exchange.com/questions/23831421/26-Oct-Please-re-open-to-re-assign-points.html

Done.

jdallain

ASKER

Thanks again! You're really good at this!

GRayL

You're welcome!

calpurnia

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.

GRayL

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.