Last row from previous date ? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Last row from previous date ?

Author

Topic

PhatMackn02
Starting Member

3 Posts

Posted - 2008-08-13 : 12:32:29

I'm trying to get all rows, relative to a computer, based on a date/time stamp of a particular date, say 6/10/2008. I can query all of the data for that date by using less than (<) and greater than (>) signs and it works fine.

The problem is that I will also need the last row from the previous day, and the first row from the next day for calculation purposes. Normally, this would be a relatively simple query adjustment but the last row could actually be yesterday, two days ago, or a week ago. The same goes for the row for the next day also, (two days ahead, a week ahead, etc.) So I don't believe I can get these rows based on the date.

I guess my question is, can you query a set of rows by date and return the first previous row, and the first following row? Here is the query I'm using:

SELECT     CONVERT(varchar, timestamp, 120) AS timestamp, event
FROM         log_null
WHERE     (timestamp > '6/9/2008 11:59:59 PM') AND (timestamp < '6/11/2008 12:00:00 AM') AND (computer_name = 'AN006P')
ORDER BY computer_name, timestamp

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-13 : 12:47:36

SELECT *
FROm log_null
WHERE timestamp =(SELECT MAX(timestamp) FROM log_null WHERE timestamp < '6/9/2008 11:59:59 PM')
AND (computer_name = 'AN006P')
will give you previous row and

SELECT *
FROm log_null
WHERE timestamp =(SELECT MIN(timestamp) FROM log_null WHERE timestamp > '6/11/2008 12:00:00 AM')
AND (computer_name = 'AN006P')

will give you next row

PhatMackn02
Starting Member

3 Posts

Posted - 2008-08-13 : 13:13:45

Thanks a lot visakh! That works beautifully.

PhatMackn02
Starting Member

3 Posts

Posted - 2008-08-13 : 13:28:34

This idea works great but I did, however, find a small flaw. I needed to have the computer name as a part of the inner query. Otherwise this works great! Thanks again!

Final query:

SELECT CONVERT(varchar, timestamp, 120) AS timestamp, event
FROM log_null
WHERE (timestamp =
(SELECT MAX(timestamp) AS Expr1
FROM log_null AS log_null_1
WHERE (timestamp < '6/10/2008 12:00:00 AM') AND (computer_name = 'AN006P')))

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources