Get Time difference between group of records

Question

I have a table with the following structure

ID         ActivityTime             Status
19        2013-08-23 14:52            1
19        2013-08-23 14:50            1
19        2013-08-23 14:45            2
19        2013-08-23 14:35            2
19        2013-08-23 14:32            1
19        2013-08-23 14:30            1
19        2013-08-23 14:25            2
19        2013-08-23 14:22            2
53        2013-08-23 14:59            1
53        2013-08-23 14:56            1
53        2013-08-23 14:57            1
53        2013-08-23 14:52            2
53        2013-08-23 14:50            2
53        2013-08-23 14:49            2
53        2013-08-23 14:18            2
53        2013-08-23 14:30            1

I want to calculate the total time difference for each ID against Status 2. For example ID 19 stayed on Status 2 from 14:35 To 14:50 (15 minutes), then 14:22 to 14:30 (8 minutes). So the total ID 19 stayed on Status 2 is 23 minutes. (In both cases I considered the minimum time for status 2 to status 1) The problem is how do I only include difference between Status where they are different in next row.

For example I would exclude the first record in the table with the status 1 and pick the second row. Same is the case with Status 2. I would pick the minimum time, calculate their difference and then add them up for multiple groups of status against each channel.

I have tried using CURSOR but can't seem to make it work. I am also pretty confident that I can achieve that in C# after getting all the data and then looping through it. But I am just wondering if there is a direct way of getting the information without a loop. I also created a SQL fiddle with data, but I can't seem to make it work. I am coming from C# background with basic knowledge of SQL. Would be glad if someone can help me out.

Answer 1

CTE (common table expressions) can be used like a specialized temp table. It allows you to (in this case) dynamically create a row number that you can later create a self join on.

I think you are looking for something like this:

--create temp table
select 19 as id,'2013-08-23 14:52' as activitytime,1 as status
into #temp
union all
select 19,'2013-08-23 14:50',1 union all
select 19,'2013-08-23 14:45',2 union all
select 19,'2013-08-23 14:35',2 union all
select 19,'2013-08-23 14:32',1 union all
select 19,'2013-08-23 14:30',1 union all
select 19,'2013-08-23 14:25',2 union all
select 19,'2013-08-23 14:22',2 union all
select 53,'2013-08-23 14:59',1 union all
select 53,'2013-08-23 14:56',1 union all
select 53,'2013-08-23 14:57',1 union all
select 53,'2013-08-23 14:52',2 union all
select 53,'2013-08-23 14:50',2 union all
select 53,'2013-08-23 14:49',2 union all
select 53,'2013-08-23 14:18',2 union all
select 53,'2013-08-23 14:30',1

--build cte table
;WITH cte
AS (
SELECT 
    *,
    ROW_NUMBER() OVER (ORDER BY  id, activitytime) AS RowNum
FROM 
    #temp
)

--query cte table, self joining row 1 to row 2 to compare.
SELECT a.id, sum(DATEDIFF(minute,b.ActivityTIme,a.ActivityTime)) as TotalTime
FROM 
 cte AS A
 LEFT OUTER JOIN cte AS B   
 ON A.RowNum = B.RowNum + 1 and a.id = b.id
where b.status = 2
group by a.id