Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Picking two most recent dates for each individual

Posted on 2008-09-30
18
Medium Priority
?
335 Views
Last Modified: 2010-04-21
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

0
Comment
Question by:jdallain
  • 8
  • 6
  • 2
  • +1
17 Comments
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 22610567
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
 
LVL 44

Expert Comment

by:GRayL
ID: 22610600
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
 

Author Comment

by:jdallain
ID: 22610605
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
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 

Author Comment

by:jdallain
ID: 22610610
Thanks GRayL! I'll give it a try.
0
 

Author Comment

by:jdallain
ID: 22610657
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
 
LVL 44

Expert Comment

by:GRayL
ID: 22610742
Check spaces and closing parentheses.
0
 

Author Comment

by:jdallain
ID: 22610766
ok
0
 

Author Comment

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

Author Comment

by:jdallain
ID: 22707689
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
 
LVL 10

Accepted Solution

by:
calpurnia earned 500 total points
ID: 22730743
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
 

Author Comment

by:jdallain
ID: 22757302
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
 
LVL 44

Expert Comment

by:GRayL
ID: 22762551
idallain:  I suggest we open up the question and you re-assign the points to calpurnia.  I will take care of it.
0
 
LVL 44

Expert Comment

by:GRayL
ID: 22762577
0
 

Author Closing Comment

by:jdallain
ID: 31501797
Thanks again! You're really good at this!
0
 
LVL 44

Expert Comment

by:GRayL
ID: 22777443
You're welcome!
0
 
LVL 10

Expert Comment

by:calpurnia
ID: 22785821
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
 
LVL 44

Expert Comment

by:GRayL
ID: 22785892
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

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

824 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question