Max Value Between Two Different Names

eusanpe · October 14th, 2010, 01:27 PM

Hello All,

I am trying to figure out how to do this. I want to get the max value of the 'BUN' result which I know how to do. I also want to get the max value between the Glucose IDG and Glucose Fasting. I haven't been able to figure it out.

The results should be:

1234 10/13/2010 17:00:00 BUN 25
1234 10/14/2010 06:00:00 Glucose Fasting 90

Any help will be appreciated.
Tony

--------------------------------------------
USE TempDB
GO

CREATE TABLE dbo.labdata
(
UserID INT,
TestDate DATETIME,
TestName VARCHAR(100),
TestResult INT
)

INSERT INTO LabData
(TestNbr,TestDate,TestName,TestResult)
SELECT '1234','10/14/2010 18:00:00','BUN','20' UNION ALL
SELECT '1234','10/13/2010 17:00:00','BUN','25' UNION ALL
SELECT '1234','10/14/2010 13:00:00','Glucose IDG','80' UNION ALL
SELECT '1234','10/14/2010 06:00:00','Glucose Fasting','90'

Old Pedant · October 14th, 2010, 02:54 PM

When you say "max value between the Glucose IDG and Glucose Fasting" do you mean the maximum *difference* between the two values for the same test?

Do you want it by TestNbr? By Date? Or what????

I will assume you want it by TestNbr *AND* TestDate.

Code:

SELECT TOP 1 L1.TestNbr, L1.TestDate, L1.TestResult, L2.TestResult, (L1.TestResult-L2.TestResult) AS TestDiff
FROM LabData AS L1, LabData AS L2
WHERE L1.TestNbr = L2.TestNbr
AND L1.TestDate = L2.TestDate
AND L1.TestName = 'Glucose Fasting'
AND L2.TestName = 'Glucose IDG'
ORDER BY TestDiff DESC

eusanpe · October 14th, 2010, 03:14 PM

Thank you for replying.

I am sorry for not making it clear.

1. I am looking for the maximum result for the BUN
2. I am looking for the maximum result for either the
(Glucose IDG result or the Glucose Fasting result) but
not both.
I want to compare the results of the Glucose IDG and the Glucose Fasting and whichever has the highest value, display it instead.

I hope this makes sense.

Thanks again,
Tony

Old Pedant · October 14th, 2010, 09:36 PM

OH! Much easier:

Code:

SELECT TOP 1 * From LabData
WHERE TestName = 'BUN'
ORDER BY TestResult DESC
UNION
SELECT TOP 1 * From LabData
WHERE TestName IN ('Glucose Fasting' ,'Glucose IDG')
ORDER BY TestResult DESC