Wrox Programmer Forums
Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2005 > SQL Server 2005
|
SQL Server 2005 General discussion of SQL Server *2005* version only.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2005 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old October 14th, 2010, 01:27 PM
Authorized User
 
Join Date: Sep 2006
Posts: 73
Thanks: 6
Thanked 0 Times in 0 Posts
Default Max Value Between Two Different Names

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 October 14th, 2010, 02:54 PM
Friend of Wrox
 
Join Date: Jun 2008
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
Default

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
 
Old October 14th, 2010, 03:14 PM
Authorized User
 
Join Date: Sep 2006
Posts: 73
Thanks: 6
Thanked 0 Times in 0 Posts
Default

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 October 14th, 2010, 09:36 PM
Friend of Wrox
 
Join Date: Jun 2008
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
Default

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





Similar Threads
Thread Thread Starter Forum Replies Last Post
Selecting MAX Value nirmal_s19 SQL Server ASP 1 May 8th, 2007 01:04 AM
Max Date kristina Crystal Reports 1 July 10th, 2005 09:03 AM
How to get max value within same id jackps SQL Server 2000 1 January 20th, 2004 07:26 AM
How to select Max hosefo81 MySQL 0 November 25th, 2003 10:27 PM
how to select Max hosefo81 PHP Databases 0 November 21st, 2003 12:58 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.