TSQL Getting Info From Another Row in the Same Table?

I am building a TSQL query to parse through a FTP log from FileZilla. I am trying to figure out if there is a way to get information from a line preceding the current one?

For example, I have parsed out the Following procedure: "STOR file.exe"

With the FileZilla is doesn't say if the STOR wass successful until the next line. So I want to check the next line and see if the STOR was successful or was unsuccessful?

Also people could try to STOR a files multiple times so I want to get the last version o开发者_运维问答f its status.

Example Info from Log file:

   (000005) 4/10/2010 14:55:30 PM - ftp_login_name (IP Address)> STOR file.exe
   (000005) 4/10/2010 14:55:30 PM - ftp_login_name (IP Address)> 150 Opening data for transfer.
   (000005) 4/10/2010 14:55:30 PM - ftp_login_name (IP Address)> 226 Transfer OK

I want to add a column in my query that says that the STOR was successful or unsuccessful.

Thanks!

Assuming you have parsed these lines into actual columns, and you have SQL server 2005 or greater. You can use CROSS APPLY example query below (untested). I hope this helps.

select o.*, prev.*
from FTPLog o
cross apply 
 (
   select top 1 *
   from FTPLog P where P.LogDate < O.LogDate 
   order by LogDate DESC
 ) prev

James has the right idea, though there may be some issues if you ever have log dates that are exactly the same (and from your sample it looks like you might). You may be able to add an identity column to force an order at the time the data is inserted, then you can use James' concept on the identity column.

More than that though, TSQL may not be the best choice for this project, at least not by itself. While there are techniques you can use to make it iterate sequentially, it is not as good for that as certain other languages are. You may want to consider parsing your files in a tool, such as Python or Perl or even C#, that is better at text processing and better at processing data sequentially.