Grab Next Row in SQL

Question

I am trying to grab a field from the next row of this database. I can use the logged on time to determine the logout time but I would like to also grab the next login time so I can determine the logout [logged off] time as well. Any suggestions? Thank you

|-LogonDate---|-PersonID-|-LoggedOnTime---|   
|5/30 12:00pm |100       |3600            | 
|5/30 01:00pm |100       |3430            |
|5/30 03:30pm |100       |3000            |

SELECT PersonID, LogonDate, LoggedOnTime
FROM Agent_Logout

Answer 1

You could use a sub-select to get the next LogonDate, then determine the difference between them.

SELECT
    a.PersonID,
    a.LogonDate,
    a.LoggedOnTime,
    (SELECT b.LogonDate
     FROM Agent_Logout AS b
     WHERE b.LogonDate > a.LogonDate
     ORDER BY b.LogonDate ASC
     LIMIT 1) AS NextLogonDate
FROM Agent_Logout AS a

If using MS SQL Server, remove LIMIT 1 and add TOP 1 here: SELECT TOP 1 b.LogonDate

EDIT: Thanks to @JYelton for pointing out TOP 1 for MS SQL Server, and ORDER BY b.LogonDate ASC

Answer 2

It is possible with a analytic function. Don't know which DBMS you use but Oracle and I think also SQL Server can do it. I did it once in Oracle. There is an excellent side explaining it here I always go there when I need this analytic functions (Usually is rather RANK). Your will need a function called LEAD. I think it would work like this:

SELECT PersonID, LogonDate, LoggedOnTime
       LEAD(LoggedOnTime, 1, 0) OVER (PARTITION BY PersonID ORDER BY LogonDate, LoggedOnTime)
FROM Agent_Logout

Let me know if this does not work as you want (Because I haven't tested it). I will then try to go and make it work. But i am very confident ... if your database allows the syntax.