Get time difference sum between tw specifio records sql

Question

2.50/5 (2 votes)

See more:

SQL-Server-2008R2

, +

I have a table with the following structure

CSS

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

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

SQL

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)

Posted 22-Jan-15 7:53am

surajemo

Updated 22-Jan-15 9:30am

Sergey Alexandrovich Kryukov

v3

Add a Solution

Comments

BacchusBeale 22-Jan-15 15:27pm

What's in Case_Status table? Why you need reference it...why you choose Id=53?

Zoltán Zörgő 23-Jan-15 4:58am

Any progress?

3 solutions

Solution 2

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

Posted 27-Jan-15 0:14am

deepakdynamite

v2

Solution 3

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

Posted 27-Jan-15 1:07am

_Asif_

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

**Zoltán Zörgő** · Accepted Answer · 2015-01-22T10:13:00

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:

SQL

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)