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
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
0
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
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
0
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