Retrieving Second Latest Time

eusanpe · September 20th, 2010, 05:28 PM

Hello All,

I am trying to write a query to get the second latest date from a table.

For instance, if you look at the example TempDB data for the TestName Glucose, The Max Date is 09/20/2010 @ 18:00. This is considered a shift date. It is charted but I am not interested in that date. I am interested in the latest date prior to the charted shift date.

In example with the TestName BUN, The Max Date is 09/19/2010 @ 06:00. This a considered a shift date also. I am not interested in that date. In this instance, there is a second date of 09/19/2010 @06:00 which is considered the latest date prior to the charted shift date.

There will always be a shift date with a time of 06:00 or 18:00. So any results that chart between 18:01 to 06:00 chart to the 06:00 shift date and any results that chart between 06:01 and 18:00 chart to the 18:00 shift date.

Any help would be appreciated.

Thanks,
Tony

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

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

INSERT INTO LabData
(TestNbr,TestDate,TestName)
SELECT '1234','09/20/2010 18:00:00','Glucose' UNION ALL
SELECT '1234','09/20/2010 17:00:00','Glucose' UNION ALL
SELECT '1234','09/20/2010 13:00:00','Glucose' UNION ALL
SELECT '1234','09/20/2010 06:00:00','Glucose' UNION ALL
SELECT '5678','09/19/2010 06:00:00','BUN' UNION ALL
SELECT '5678','09/19/2010 06:00:00','BUN' UNION ALL
SELECT '5678','09/18/2010 21:00:00','BUN'

Old Pedant · September 20th, 2010, 06:48 PM

You want the second latest PER TestName? Or PER TestNbr?

Or you want the second latest for a *particular* TestName or TestNbr??

I'm going to assume you ant second latest PER TestNbr, for all TestNbr vales.

So:

Code:

SELECT T1.TestNbr, MAX(T1.TestDate) AS secondLatestDateTime
FROM LabData AS T1, 
    ( SELECT TestNbr, MAX(TestDate) AS LatestDateTime
      FROM LabData 
      GROUP BY TestNbr ) AS T2
WHERE T1.TestNbr = T2.TestNbr
AND T1.TestDate < T2.LatestDateTime
GROUP BY T1.TestNbr
ORDER BY T1.TestNbr