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 September 20th, 2010, 05:28 PM
Authorized User
 
Join Date: Sep 2006
Posts: 73
Thanks: 6
Thanked 0 Times in 0 Posts
Default Retrieving Second Latest Time

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 September 20th, 2010, 06:48 PM
Friend of Wrox
 
Join Date: Jun 2008
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
Default

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





Similar Threads
Thread Thread Starter Forum Replies Last Post
Retrieving Current Date/Time with Javascript Neal XSLT 5 January 23rd, 2009 11:28 AM
HELP!!! retrieving time and date from SQL 2005 wearyrecca Visual Basic 2005 Basics 2 September 11th, 2007 10:28 AM
latest version crmpicco Beginning VB 6 8 March 24th, 2005 03:51 PM
What's the latest C# edition? Daemon BOOK: ASP.NET Website Programming Problem-Design-Solution 1 January 18th, 2004 05:53 PM
Retrieving file create time [email protected] ADO.NET 1 July 9th, 2003 09:22 AM





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