Time difference between two rows in sql server 2012 or 2008

Question

I my trying to log the user page visit, and am logging the page name, session id, entry time only

i need to find the duration of the each page, which means the entry time of the next page is the exit time of the previous page.

I have a table like this:

    sessionId   |     PageName     |    EntryTime
--------------------------------------------------------------
    xxxxxx1     |      SignIn      | 2015-10-13 13:03:45.000
    xxxxxx1     |      Landing     | 2015-10-13 13:04:00.000
    xxxxxx1     |      Users       | 2015-10-13 13:04:30.000

I need the output like this:

    sessionId   |     PageName     |    EntryTime             |  Duration(mm:ss)
--------------------------------------------------------------------------
    xxxxxx1     |      SignIn      | 2015-10-13 13:03:45.000  |  00:15
    xxxxxx1     |      Landing     | 2015-10-13 13:04:00.000  |  00:30
    xxxxxx1     |      Users       | 2015-10-13 13:04:30.000  |  00:00

I tried this:

WITH LogsTable AS
(
    select ROW_NUMBER() OVER (ORDER BY [EntryTime]) -- Create an index number ordered by time.
         AS [Sequence], EntryTime from TblLOGPageVisits  where SessionIdUnique = '3t5sk1t0ix5wnj22yshodepy'
)
SELECT *,
       ISNULL(DATEDIFF(SECOND, 
                      (SELECT other.EntryTime 
                              FROM 
                              (
                              select ROW_NUMBER() OVER (ORDER BY [EntryTime]) AS [Sequence], 
                              * from TblLOGPageVisits  where SessionIdUnique = '3t5sk1t0ix5wnj22yshodepy') as Other 
                              WHERE other.Sequence = LogsTable.Sequence - 1 
                              ), 
                       LogsTable.EntryTime
                       ), 0) 
    AS Duration
FROM LogsTable;

Its giving the output as:

  Sequence  |  sessionId   |     PageName     |    EntryTime             |  Duration(mm:ss)
--------------------------------------------------------------------------------------------
      1     |  xxxxxx1     |      SignIn      | 2015-10-13 13:03:45.000  |  0
      2     |  xxxxxx1     |      Landing     | 2015-10-13 13:04:00.000  |  1500
      3     |  xxxxxx1     |      Users       | 2015-10-13 13:04:30.000  |  3000

How to achieve my required output ?

Whats going wrong in my query?

Answer 1

If you're having a chance to use SQL Server 2012, please use LEAD() function as suggested. If not - you can use ROW_NUMBER() workaround - but you'll have to join same table twice, unfortunately.

DECLARE @LogsTable TABLE
(
    sessionId VARCHAR(10)
    , PageName VARCHAR(10)
    , EntryTime DATETIME2
);

INSERT INTO @LogsTable
VALUES ('xxxxxx1', 'SignIn', '2015-10-13 13:03:45.000')
    , ('xxxxxx1', 'Landing', '2015-10-13 13:04:00.000')
    , ('xxxxxx1', 'Users', '2015-10-13 13:04:30.000');

;WITH cte (sessionId, PageName, EntryTime, RN, PRN)
AS (
    SELECT sessionId
        , PageName
        , EntryTime
        , ROW_NUMBER() OVER(PARTITION BY sessionId ORDER BY EntryTime)
        , ROW_NUMBER() OVER(PARTITION BY sessionId ORDER BY EntryTime) + 1
    FROM @LogsTable
    )
SELECT C1.RN AS Sequence
    , C1.sessionId
    , C1.PageName
    , C1.EntryTime
    , COALESCE(CAST(DATEDIFF(MM, C1.EntryTime, C2.EntryTime) AS VARCHAR(10)) + ':' + CAST(DATEDIFF(SS, C1.EntryTime, C2.EntryTime) AS VARCHAR(10)), '00:00') AS Duration
FROM cte AS C1
LEFT JOIN cte AS C2
    ON C2.sessionId = C1.sessionId
    AND C2.RN = C1.PRN;

OUTPUT:

╔══════════╦═══════════╦══════════╦═════════════════════════════╦══════════╗
║ Sequence ║ sessionId ║ PageName ║          EntryTime          ║ Duration ║
╠══════════╬═══════════╬══════════╬═════════════════════════════╬══════════╣
║        1 ║ xxxxxx1   ║ SignIn   ║ 2015-10-13 13:03:45.0000000 ║ 0:15     ║
║        2 ║ xxxxxx1   ║ Landing  ║ 2015-10-13 13:04:00.0000000 ║ 0:30     ║
║        3 ║ xxxxxx1   ║ Users    ║ 2015-10-13 13:04:30.0000000 ║ 00:00    ║
╚══════════╩═══════════╩══════════╩═════════════════════════════╩══════════╝

Answer 2

For SQL Server 2012, you can use the LEAD function.

SQL Fiddle

WITH Cte AS(
    SELECT *,
        Duration = DATEDIFF(SECOND, EntryTime, LEAD(EntryTime) OVER(ORDER BY EntryTime))
    FROM LogsTable
)
SELECT 
    sessionId,
    PageName,
    EntryTime,
    Duration =
        CASE
            WHEN Duration IS NULL THEN '00:00'
            ELSE
                RIGHT('0' + CONVERT(VARCHAR(10), Duration / 60), 2) + ':' + 
                RIGHT('0' + CONVERT(VARCHAR(10), Duration % 60), 2)
        END
FROM Cte

You can add an additional PARTITION BY for different sessionIds.