Solved

Picking two most recent dates for each individual

Posted on 2008-09-30
18
327 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
18 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
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 

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 125 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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

828 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