获取两个特定记录sql之间的时差 [英] Get time difference sum between tw specifio records sql
问题描述
我有一个包含以下结构的表格
I have a table with the following structure
AutoID ActivityTime StatusId
1 2013-08-23 14:52 15
2 2013-08-23 14:50 16
3 2013-08-23 14:45 1
4 2013-08-23 14:35 15
5 2013-08-23 14:32 15
6 2013-08-23 14:30 16
7 2013-08-23 14:25 15
8 2013-08-23 14:22 16
9 2013-08-23 14:25 1
10 2013-08-23 14:22 16
我想计算每个AutoID的总时差针对状态15，然后是16.
例如，ID 1,2的状态ID为15和16，所以我想获得这些记录之间的ActivityTime差异
再次为5,6 ID和7,8 id等
预期
时间差和1,2之间是2分钟
时间差与5,6之间是2分钟
时间差与7,8之间是3分钟
所以总分钟将是7分钟
i想要时差的总和可以帮助我
I want to calculate the total time difference for each AutoID against Status 15 followed by 16.
For example ID 1,2 are having status id as 15 and 16 so i want to get the ActivityTime Difference between those records
Again for 5,6 ID and again 7,8 id etc
Expected out
time difrrence between and 1,2 is 2 minutes
time difrrence between and 5,6 is 2 minutes
time difrrence between and 7,8 is 3 minutes
so the the total minutes will be 7 minutes
i want sum of time difference can any one help me
select
datediff(HOUR,A.CreatedOn,B.CreatedOn),
A.*
from [dbo].[Case_Status] A
LEFT OUTER JOIN [Case_Status] B
ON A.StatusId > B.StatusId + 1
where A.[Case_Id]=53
AND A.StatusId in(15,16)
推荐答案
实际上，你的帖子有些模糊，因为你不喜欢不要告诉这个表的名称，但你似乎在你的方法中使用它，但是使用其他字段名称......
仍然，假设你提供的结构和Case_Status作为名称，这里你有两种工作方法：
这将返回你需要的东西。当然，如果需要，可以在存储过程中实现它。
Actually, your post is somewhat vague, as you don't tell the name of this table, but you seem to be using it in your approach, but with other field names...
Still, assuming the structure you have provided and Case_Status as name, here you have two working approaches:
This will return what you need. Of course, you can implement it in a stored procedure if you want.
declare @prev_t datetime;
declare @prev_s int = 0;
declare @_id int;
declare @_time datetime;
declare @_stat int;
declare @sumdiff int = 0;
declare cs cursor for
select * from Case_Status where StatusId in (15,16) order by AutoId;
OPEN cs
FETCH NEXT FROM cs
INTO @_id, @_time, @_stat
WHILE @@FETCH_STATUS = 0
BEGIN
IF @_stat = 16 AND @prev_s = 15
BEGIN
SET @sumdiff = @sumdiff + DATEDIFF(minute, @_time, @prev_t)
END
SET @prev_t = @_time
SET @prev_s = @_stat
FETCH NEXT FROM cs
INTO @_id, @_time, @_stat
END
CLOSE cs
DEALLOCATE cs
SELECT @sumdiff
实际上，你的方法也有效，但是像这样：
Actually, your approach is also working, but like this:
select SUM(DATEDIFF(minute, B.ActivityTime, A.ActivityTime))
from Case_Status A
inner join Case_Status B ON (B.AutoId = A.AutoId+1 AND A.StatusId = 15 AND B.StatusId=16)
使用此：
http://blog.sqlauthority.com / 2011/11/15 / sql-server-introduction-to-lead-and-lag-analytic-functions -in-sql-server-2012 / [ ^ ]
如果你采用这种方法，你将不需要循环游标......
Use this :
http://blog.sqlauthority.com/2011/11/15/sql-server-introduction-to-lead-and-lag-analytic-functions-introduced-in-sql-server-2012/[^]
If you go through this approach, You won't need to loop through Cursors...
试试这个
Try this
DECLARE @TBL TABLE
(
AUTO_ID INT IDENTITY,
ACTIVITY_TIME DATETIME,
STATUS_ID INT
)
INSERT INTO @TBL(ACTIVITY_TIME,STATUS_ID)
SELECT '2013-08-23T14:52:00' ACTIVITY_TIME, 15 STATUS_ID
UNION ALL
SELECT '2013-08-23T14:50:00' , 16
UNION ALL
SELECT '2013-08-23T14:45:00' , 1
UNION ALL
SELECT '2013-08-23T14:35:00' , 15
UNION ALL
SELECT '2013-08-23T14:32:00' , 15
UNION ALL
SELECT '2013-08-23T14:30:00' , 16
UNION ALL
SELECT '2013-08-23T14:25:00' , 15
UNION ALL
SELECT '2013-08-23T14:22:00' , 16
UNION ALL
SELECT '2013-08-23T14:25:00' , 1
UNION ALL
SELECT '2013-08-23T14:22:00' , 16
SELECT DATEDIFF(mi, T2.ACTIVITY_TIME, T1.ACTIVITY_TIME)
FROM @TBL T1
INNER JOIN @TBL T2 ON T1.AUTO_ID + 1 = T2.AUTO_ID
WHERE T1.STATUS_ID = 15
AND T2.STATUS_ID = 16
SELECT Sum(DATEDIFF(mi, T2.ACTIVITY_TIME, T1.ACTIVITY_TIME))
FROM @TBL T1
INNER JOIN @TBL T2 ON T1.AUTO_ID + 1 = T2.AUTO_ID
WHERE T1.STATUS_ID = 15
AND T2.STATUS_ID = 16
这篇关于获取两个特定记录sql之间的时差的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
