1

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.

0

1 Answer 1

1

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
2
  • This is working accordingly but if I want few timediff columns and display the ticketid which haven't any timediff value because it is not displaying them then what should I try?
    – meenakshi
    Mar 12, 2015 at 5:37
  • okay so in the @ query, it's actually assembling your pivot statement dynamically. So to visualize, I would select from @ query to look at the code. To play with everything, it might be easier if insert into target table so add "INTO target table' before the from clause. Then play with your target table. To display ticketids with no value it would simply use UNION ALL or if you use a target table, then just insert them into the ticketID column since they have no time values, it would be very simple.
    – Stephan
    Mar 12, 2015 at 13:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.