从多个不同的行中查找MSSQL中2个日期之间的差异

When using MSSQL, is there any way using SQL statements to DATEDIFF the dates from 2 different rows. We are trying to gauge response times.

使用MSSQL时,有没有办法使用SQL语句来DATEDIFF来自2个不同行的日期。我们正在努力衡量响应时间。

We have a messaging system that stores the UniqueKey, Note, DateNoteSent, and User that sent the note.

我们有一个消息传递系统,用于存储发送注释的UniqueKey,Note,DateNoteSent和User。

Data Sample:

UniqueKey      Note                 User     DateOfNote                 
L4H2390039D2   This is a test.      23       2018-01-30 16:15:14.2965479
L4H2390039D2   Test received.       15       2018-01-30 18:10:00.3482044
DH38D2DJ8382   Call me ASAP.        17       2018-01-30 16:22:34.1971033
DH38D2DJ8382   Will do.             35       2018-01-30 16:25:34.1749088
DH38D2DJ8382   Sorry I missed you.  17       2018-01-30 16:28:34.1991463

Is it possible to find the difference between the date/times in messages with the same UniqueKey?

是否可以找到具有相同UniqueKey的消息中的日期/时间之间的差异?

Some of the messages have more than 2 messages in the chain and would.

一些消息在链中有超过2条消息,并且会。

I would the like to have a final column that shows the time duration from the previous message named ResponseTime that shows either a time ie: 12:34:33 or 1 hr 37mins.

我希望有一个最后一列显示上一条名为ResponseTime的消息的持续时间,该消息显示时间即:12:34:33或1小时37分钟。

Thanks for any help.

谢谢你的帮助。

2 个解决方案

#1

Here is a solution using LAG function. Result is in format hhh:mm:ss

这是使用LAG功能的解决方案。结果格式为hhh:mm:ss

select
    UniqueKey, Note, [User], DateOfNote
    , concat(right(concat('000',diff/3600), 3), ':', right(concat('00',diff%3600/60), 2), ':', right(concat('00',diff%60), 2))
from (
    select 
        *, diff = datediff(ss, lag(DateOfNote) over (partition by UniqueKey order by DateOfNote), DateOfNote)
    from 
        #Table1
) t

#2

If you are on SQL prior to 2012 then alternative to LEAD function:

如果你在2012之前使用SQL,那么替代LEAD功能:

create Table #Table1 (
    UniqueKey varchar(20),
    Note varchar(100),
    [User] varchar(10),
    DateOfNote datetime
    );

-- add some records 
Insert into #Table1 (UniqueKey,Note,[User],DateOfNote) values  ('L4H2390039D2','This is a test.','23',CAST(N'2018-01-30 16:15:14.296' AS DateTime))
Insert into #Table1 (UniqueKey,Note,[User],DateOfNote) values  ('L4H2390039D2','Test received.','15',CAST(N'2018-01-30 18:10:00.348' AS DateTime))
Insert into #Table1 (UniqueKey,Note,[User],DateOfNote) values  ('DH38D2DJ8382','Call me ASAP','17',CAST(N'2018-01-30 16:22:34.197' AS DateTime))
Insert into #Table1 (UniqueKey,Note,[User],DateOfNote) values  ('DH38D2DJ8382','Will do.','35',CAST(N'2018-01-30 16:25:34.174' AS DateTime))
Insert into #Table1 (UniqueKey,Note,[User],DateOfNote) values  ('DH38D2DJ8382','Sorry I missed you.','17',CAST(N'2018-01-30 16:28:34.199' AS DateTime))
Insert into #Table1 (UniqueKey,Note,[User],DateOfNote) values  ('DH38D2DJ8382','Sorry I missed you.','17',CAST(N'2018-02-02 08:01:34.199' AS DateTime))


select t.Number,t.UniqueKey,t.DateofNote,tnext.DateofNote,datediff(minute, t.DateofNote, tnext.DateofNote) as DiffMinutes,CONVERT(varchar(12), 
       DATEADD(minute, DATEDIFF(minute, t.DateofNote, tnext.DateofNote), 0), 114) as hourMinute,
        cast(datediff(minute, t.DateofNote, tnext.DateofNote) / 1440 as varchar) + ' days ' + cast((datediff(minute, t.DateofNote, tnext.DateofNote) % 1440) / 60  as varchar) + ' hours ' + cast(datediff(minute, t.DateofNote, tnext.DateofNote) % 60 as varchar) + ' minutes' as DaysHoursMinutes

from (
select * 
,ROW_NUMBER() over ( ORDER BY UniqueKey,DateOfNote) AS Number
from #Table1 

)t join
     (select * 
,ROW_NUMBER() over ( ORDER BY UniqueKey,DateOfNote) AS Number
from #Table1 

) tnext
     on t.Number = tnext.Number - 1 and t.UniqueKey = tnext.UniqueKey
order by t.UniqueKey,t.DateofNote

#1