SQL 查询，用于将每个票证的日期时间差计算为每个工单的持续时间

Question

我有一个表，其中列有票证ID，所有者，拥有日期。

ticketid owner          owndate
1050    REID        2004-09-15 09:50:41
1054    SINCLAIR    2004-09-15 09:52:14
1069    WILSON      2004-09-24 08:17:44
1069    REID        2004-09-24 08:17:55
1073    SINCLAIR    2004-09-24 08:44:57
1026    (null)      2004-09-24 08:46:23
1074    REID        2004-09-24 08:55:31
1075    REID        2004-09-24 08:58:29
1028    REID        2004-09-24 08:59:09
1028    (null)      2004-09-24 08:59:44
1076    SINCLAIR    2004-09-24 09:00:46
1029    SINCLAIR    2004-09-24 09:01:54
1077    SINCLAIR    2004-09-24 09:03:17
1078    REID        2004-09-24 09:05:12
1031    SINCLAIR    2004-09-24 09:06:04
1084    REID        2004-09-24 09:10:22
1086    REID        2004-09-24 09:11:16
1088    SINCLAIR    2004-09-24 09:16:52
1036    SINCLAIR    2004-09-24 09:17:38
1089    REID        2004-09-24 09:18:31
1037    REID        2004-09-24 09:22:57
1004    (null)      2004-09-24 09:24:21
1041    REID        2004-09-24 09:57:47

我想将输出列显示为票证ID，持续时间1，持续时间2，持续时间3...其中持续时间是特定票证ID的两个连续行之间的时间差，如...

ticketid     timeduration1     timeduration2         timeduration3     ...
1001        1min 43sec       4min 18sec        74days 19hrs 34min 59sec ...

Answer 1

最后，

我以完美的输出进行了查询。

select ticketid,
owner1=CONVERT(varchar(10),owner2-owner1,108),
owner2=CONVERT(varchar(10),owner3-owner2,108),
owner3=CONVERT(varchar(10),owner4-owner3,108),
owner4=CONVERT(varchar(10),owner4,108)from
(
select ticketid,owndate,'owner'+cast(ROW_NUMBER() over 
(partition by ticketid order by owndate) as varchar(10)) as sno from   tkownerhistory                
) tbl
pivot
(
max(owndate)
for sno in(owner1,owner2,owner3,owner4)
)pvt'