Top 2 within a date range.

G

Guest

I am looking for a query that will show the last two occurences (by date).
The date are within a range of the year 2000 through 2006.

The issue I am coming accross is if the patient has an occurance in 2007, it
is counted as one of the two.

Below are what I have so far

---------------------------------------------
Subquery Name (qry-Last2VL-2000-2006-SubQuery)

SELECT
cptViralLoad.RecordID,
cptViralLoad.PatientID,
cptViralLoad.ViralLoadDate,
cptViralLoad.ViralLoad
FROM
cptViralLoad
WHERE
(((cptViralLoad.ViralLoadDate)>=#1/1/2000# And
(cptViralLoad.ViralLoadDate)<=#12/31/2006#))
ORDER BY
cptViralLoad.ViralLoadDate DESC;


Main Query name (qry-Test-Last2VL)

SELECT
[qry-Last2VL-2000-2006-SubQuery].PatientID,
[qry-Last2VL-2000-2006-SubQuery].ViralLoadDate,
[qry-Last2VL-2000-2006-SubQuery].ViralLoad,
[qry-Last2VL-2000-2006-SubQuery].RecordID,
cptPatientDemographics.MedicalRecordID,
[lastname] & ", " & [firstname] AS Client
FROM
cptPatientDemographics INNER JOIN [qry-Last2VL-2000-2006-SubQuery] ON
cptPatientDemographics.PatientID = [qry-Last2VL-2000-2006-SubQuery].PatientID
ORDER BY [lastname] & ", " & [firstname];
---------------------------------------------

I am sure the answer is here. Ihave tried using the example found here:

http://allenbrowne.com/subquery-01.html#TopN

I'm missing something.




Thank for any help.
Glenn_H
 
M

Michel Walsh

Yes, indeed, you can do a TOP 2 in the inner most query, based on dateStamp,
but in DESCendant order of dateStamp.

You can also rank per group (which can, sometimes, be faster, being based on
a join rather than on a sub-query):


SELECT a.patientID, a.dateStamp, LAST(a.someOtherInfo)
FROM tableName AS a INNER JOIN tableName AS b
ON (a.patientID=b.patientID AND a.dateStamp<= b.dateStamp)
GROUP BY a.patientID, a.dateStamp
HAVING COUNT(*) <= 2



It uses two references to your table (tableName), one aliased as a, one as
b, but for a given (a.patientID, a.dateStamp) the alias b is limited to
records having the same patientID , and date in the future of a.dateStamp.
So, there will be only 1 such record if that is the greatest (latest) date,
and 2 records if a.dateStamp is the preceding latest date. COUNT(*) is thus
nothing more than the RANK, and <=2 limits the whole data set to the top 2
latest date, per client, indeed.

Hoping it may help,
Vanderghast, Access MVP
 
G

Guest

Glenn_H said:
I am looking for a query that will show the last two occurences (by date).
The date are within a range of the year 2000 through 2006.

The issue I am coming accross is if the patient has an occurance in 2007, it
is counted as one of the two.

Below are what I have so far

---------------------------------------------
Subquery Name (qry-Last2VL-2000-2006-SubQuery)

SELECT
cptViralLoad.RecordID,
cptViralLoad.PatientID,
cptViralLoad.ViralLoadDate,
cptViralLoad.ViralLoad
FROM
cptViralLoad
WHERE
(((cptViralLoad.ViralLoadDate)>=#1/1/2000# And
(cptViralLoad.ViralLoadDate)<=#12/31/2006#))
ORDER BY
cptViralLoad.ViralLoadDate DESC;


Main Query name (qry-Test-Last2VL)

SELECT
[qry-Last2VL-2000-2006-SubQuery].PatientID,
[qry-Last2VL-2000-2006-SubQuery].ViralLoadDate,
[qry-Last2VL-2000-2006-SubQuery].ViralLoad,
[qry-Last2VL-2000-2006-SubQuery].RecordID,
cptPatientDemographics.MedicalRecordID,
[lastname] & ", " & [firstname] AS Client
FROM
cptPatientDemographics INNER JOIN [qry-Last2VL-2000-2006-SubQuery] ON
cptPatientDemographics.PatientID = [qry-Last2VL-2000-2006-SubQuery].PatientID
ORDER BY [lastname] & ", " & [firstname];
---------------------------------------------

I am sure the answer is here. Ihave tried using the example found here:

http://allenbrowne.com/subquery-01.html#TopN

I'm missing something.




Thank for any help.
Glenn_H
Click to expand...


This is what comes from using the
http://allenbrowne.com/subquery-01.html#TopN example.

-----(Subquery)-----
SELECT cptViralLoad.PatientID, cptViralLoad.ViralLoadDate,
cptViralLoad.ViralLoad, cptViralLoad.RecordID
FROM cptViralLoad
WHERE (((cptViralLoad.RecordID) In (Select TOP 2 RecordID
FROM cptViralLoad AS Dupe
WHERE Dupe.PatientID = cptViralLoad.PatientID
Order By Dupe.ViralLoadDate DESC, Dupe.RecordID DESC)))
ORDER BY cptViralLoad.PatientID, cptViralLoad.ViralLoadDate DESC ,
cptViralLoad.RecordID;


-----(Main Query)-----
SELECT cptPatientDemographics.MedicalRecordID AS MRN, [lastname] & ", " &
[firstname] AS Client, [qry-Last_2_ViralLoads].ViralLoadDate,
[qry-Last_2_ViralLoads].ViralLoad
FROM cptPatientDemographics INNER JOIN [qry-Last_2_ViralLoads] ON
cptPatientDemographics.PatientID=[qry-Last_2_ViralLoads].PatientID
ORDER BY [lastname] & ", " & [firstname],
[qry-Last_2_ViralLoads].ViralLoadDate DESC;
 
Top