Join Last Record Based on TimeStamp

I have a access log, which records usernames and a timestamp at various points in the application. I want to see if I can approximate the amount of time they are spending in the application. Basically I want to join 开发者_运维知识库each record to the last access record based on the timestamp, so I can figure out the time difference.

I can't think of how to join to the first record whose timestamp is less than the current record. What is the way to do this?

Try something like this:

SELECT A.UserName, A.EntryDate, DATEDIFF(ss,A.EntryDate, B.EntryDate) SecondsDuration
FROM YourTable A
OUTER APPLY (SELECT MAX(EntryDate) EntryDate
             FROM YourTable
             WHERE UserName = A.UserName AND EntryDate < A.EntryDate) B

I can't think of how to join to the first record whose timestamp is less than the current record. What is the way to do this?

You almost gave yourself the answer:

Something similar to below should work:

SELECT
   lt.*,
   lastentry.timeofEntryColumn,
   DATEDIFF( second, lt.timeofEntryColumn, lt.timeofEtnryColumn) AS DifferenceinSeconds
FROM logTable lt
LEFT OUTER JOIN logtable AS lastentry
ON lastentry.ID = (SELECT TOP 1 ID FROM logtable lt2
          WHERE lt2.timeofEntryColumn < lt.timeofEntryColumn
          ORDER BY lt2.timeofEntryColumn desc)

Of course, you should add some more to this: maybe a condition to the outer join that requires the userID match...