Most Recent, Min and Max Values
Hello All,
I have to come up with a query that will pull out the most recent value within a two hour time frame from the current time. If the value exists then I need to pull out the minimum and maximum values within a 24 hour window from the time the most recent value was charted. The most recent value could be the minimum or maximum value too. Is there a way to do it with one query. I can do it with two queries.
Is there a way to display everything on one row for simplicity?
So if a value is charted at 11:00 like in the example, I need to pull the min and max values from 11:00 today looking back to yesterday at 11:00.
Thanks,
Tony
--------------------------------------------
USE TempDB
GO
CREATE TABLE dbo.labdata
(
ClientID INT,
TestDate DATETIME,
TestName VARCHAR(100),
TestValue INT
)
INSERT INTO LabData
(ClientID,TestDate,TestName,TestValue)
SELECT '1234','07/13/2011 01:00:00','Glucose', '100' UNION ALL
SELECT '1234','07/13/2011 02:00:00','Glucose', '200' UNION ALL
SELECT '1234','07/13/2011 03:00:00','Glucose', '300' UNION ALL
SELECT '1234','07/13/2011 11:00:00','Glucose', '400' UNION ALL
SELECT '2345','07/13/2011 05:00:00','BUN', '10' UNION ALL
SELECT '2345','07/13/2011 06:00:00','BUN', '30' UNION ALL
SELECT '2345','07/13/2011 07:00:00','BUN', '20' UNION ALL
SELECT '2345','07/13/2011 11:00:00','BUN', '15' UNION ALL
SELECT '3456','07/12/2011 01:00:00','CREATININE', '0.5' UNION ALL
SELECT '3456','07/13/2011 11:00:00','CREATININE', '0.6'
----------------------------------
Expected results:
1234 07/13/2011 11:00:00 Glucose 400 (Most recent value)
1234 07/13/2011 01:00:00 Glucose 100 (Min Value)
1234 07/13/2011 11:00:00 Glucose 400 (Max Value)
2345 07/13/2011 11:00:00 BUN 15 (The most recent value)
2345 07/13/2011 05:00:00 BUN 10 (Min Value)
2345 07/13/2011 06:00:00 BUN 30 (Max Value)
3456 07/13/2011 11:00:00 CREATININE 0.6 (Most recent value)
3456 07/13/2011 11:00:00 CREATININE 0.6 (Min Value)
3456 07/13/2011 11:00:00 CREATININE 0.6 (Max Value)
|
