Sql-server – How calculate the time difference between two consecutive rows

sql serversql-server-2008

I have a table tickets with ticketid,owner, owndate columns. The table is as..

ticketid     owner      ownerdate
1001       LEWIS     2004-06-18 14:15:11
1001       WILSON    2004-06-18 14:16:54
1001       WILSON    2004-06-18 14:21:12
1001      (null)     2004-09-01 09:56:11
1001      CALDONE   2005-02-02 08:38:28
1001      SINCLAIR  2005-02-02 08:54:02
1002      (null)    2005-02-02 08:40:06
1002      REID      2015-01-16 15:18:05
1002      SMALL     2015-01-16 15:19:53
1002      WILSON    2004-06-18 14:33:47
1002      (null)    2004-08-31 15:12:46
1002      (null)    2004-09-24 10:03:09
1003      RAMSDALE  2004-09-24 10:04:24
1003      MOTIKA    2004-08-31 14:51:45
1003      (null)    2004-08-31 15:05:50
1003      MURTHY    2004-09-02 14:50:28
1004      (null)    2004-08-31 15:28:37
1004      (null)    2004-09-24 09:24:21
1005      MOTIKA    2004-08-31 14:47:19
1005      MOTIKA    2004-08-31 17:20:33
1005      (null)    2004-08-31 17:23:42
1005      WILSON    2004-09-02 16:29:43
1005       DALEY    2004-09-15 08:45:27
1005      SINCLAIR  2004-09-15 08:34:34
1006      REID      2004-09-15 08:44:16

I need the output as…

ticketid          timediff1      timeidff2         timediff3      ...
1001             1 min 43 sec      4min 18         6 min 1sec     ...
...                 ...              ...               ...

Here the timediff1 is the time difference for any distinct ticket id for owner1 and timediff2 is time difference for 2nd owner and so on.

Best Answer

This is a lot of code with tables selecting on tables and a dynamic pivot statement at the end. Let me know if you have any questions or any things you want me to change.

SELECT  ROW_NUMBER() OVER (PARTITION BY ticketid ORDER BY ownerdate) Ticket_OrderID,
        ticketID,
        [owner],
        ownerdate INTO #tempTable
FROM yourTable
--WHERE [owner] IS NOT NULL

SELECT  A.ticketid,
        'timediff' + CAST(ROW_NUMBER() OVER (PARTITION BY A.ticketID ORDER BY A.ticketID,A.ownerdate) AS VARCHAR(100)) time_diff_owner,
        CAST(DATEDIFF(MINUTE,A.ownerdate,B.ownerdate) AS VARCHAR(100)) + 'min' +
        CAST(DATEDIFF(SECOND,A.ownerdate,B.ownerdate)%60 AS VARCHAR(100)) + 'sec' time_diff_sec 
        INTO #tempTable2
FROM    #tempTable A
INNER JOIN #tempTable B
ON  A.Ticket_OrderID = B.Ticket_OrderID - 1
    AND A.ticketID = B.ticketID

DECLARE @cols AS NVARCHAR(MAX),
        @query  AS NVARCHAR(MAX);

SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(time_diff_owner) 
            FROM #tempTable2
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

SET @query = 
'SELECT ticketid,' + @cols + ' FROM #tempTable2 A
PIVOT (
     MAX(time_diff_sec) FOR time_diff_owner IN (' + @cols + ')
) pvt'

EXEC (@query)

DROP TABLE #tempTable
DROP TABLE #tempTable2

Results:

ticketid timediff1      timediff2       timediff3       timediff4       timediff5
---------------------------------------------------------------------------------
1001     1min43sec      5min18sec       107735min59sec  221682min17sec  16min34sec
1002     106599min59sec 34251min23sec   188557min57sec  5234798min59sec 1min48sec
1003     14min5sec      2865min38sec    31394min56sec   NULL            NULL
1004     34196min44sec  NULL            NULL            NULL            NULL
1005     153min14sec    3min9sec        2826min1sec     18245min51sec   11min53sec
Related Question