Solved

Picking two most recent dates for each individual

Posted on 2008-09-30
18
323 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
 

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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

746 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now