Top Values...?

[thebigpahoot]

Hello,

I'm working with a query that is supposed to give me a patient's top
lab values in a certain period of time (i.e., after procedure but
before discharge). I can't seem to get it right. I'll put Top Value as
1, but instead of giving me the top value for each procedure, it
simply gives me the top value of the first patient that pops up IN the
time period I specify. Here's what I've written...

SELECT dbo_Demographics.Patient_ID, dbo_Demographics.Last_Name,
dbo_Event_Cath.Date_of_Cath, dbo_Lab_Results.Test_Date,
dbo_Lab_Results.Test_Time, dbo_Lab_Results.Test,
dbo_Lab_Results.Test_Result
FROM dbo_Event_Cath INNER JOIN (dbo_Lab_Results INNER JOIN
dbo_Demographics ON dbo_Lab_Results.SS_Patient_ID =
dbo_Demographics.SS_Patient_ID) ON dbo_Event_Cath.SS_Patient_ID =
dbo_Lab_Results.SS_Patient_ID
GROUP BY dbo_Demographics.Patient_ID, dbo_Demographics.Last_Name,
dbo_Event_Cath.Date_of_Cath, dbo_Event_Cath.Discharge_Date,
dbo_Lab_Results.Test_Date, dbo_Lab_Results.Test_Date,
dbo_Lab_Results.Test_Time, dbo_Lab_Results.Test,
dbo_Lab_Results.Test_Result
HAVING (((dbo_Event_Cath.Date_of_Cath) Between #10/1/2006# And
#10/2/2006#) AND ((dbo_Lab_Results.Test_Date)>=[dbo_Event_Cath]!
[Date_of_Cath]) AND ((dbo_Lab_Results.Test_Date)<=[dbo_Event_Cath]!
[Discharge_Date]) AND ((dbo_Lab_Results.Test) Like "*CK-MB*"));

In the above query, I turned the Top Value to ALL, and all values in
that time frame were shown. When I change it to Top Value is 1 (first
line of the above = SELECT TOP 1 dbo_Demographics.Patient_ID....) it
only gives me one value and one value alone, which isn't even the top
value from the time period that I specified.

Does anyone have any experience in this avenue? I'd greatly appreciate
your help.

Thanks in advance.. it means a lot.

-Kevin P.

 

[Dale Fye]

Two issues:

1. You never filter on Patient_ID, so I think you will get all the results
for all the patients in the appropriate time period.

2. Because you are not ordering the results, your query just returns the
first record that meets the criteria. Add a ORDER BY clause like:

ORDER BY dbo_Lab_Results.Test_Result DESC

HTH
Dale