Sum rows on basis of current and next row

Question

I have an input in following format:

I have to find the difference of the stop of current row and the start of next row, and if the difference is less than 25, i need to sum the values in [TimeDiff_Start_Stop]. If the difference is more than 25 i do not need to do the sum.

As in the above image, difference

between Stop of Row 1 and Start of Row 2 is 13, between Stop of Row 2 and Start of Row 3 is 2, between Stop of Row 3 and Start of Row 4 is 1, between Stop of Row 4 and Start of Row 5 is 3, between Stop of Row 5 and Start of Row 6 is 8,
but the difference between Stop of Row 6 and Start of Row 7 is 37, therefore only [TimeDiff_Start_Stop] of first 6 rows is summed up, producing the row 1 in output.

Moving further difference between Stop of Row 7 and Start of Row 8 is 20, therefore [TimeDiff_Start_Stop] of row 7 and row 8 is summed up, producing the row 2 in output.

Required output

How should i achieve this ?

Please find below scripts for input and output:

Input:

select 'Sample' as COL1,'1' AS COL2,1 as 'RN','2016-05-09 02:45:18.239669' AS Start,'2016-05-09 02:45:25.837316' as Stop,7 as TimeDiff_Start_Stop
union
select 'Sample' as COL1,'1' AS COL2,2 as 'RN','2016-05-09 02:45:38.809919' AS Start,'2016-05-09 02:46:59.856081' as Stop,81 as TimeDiff_Start_Stop
union
select 'Sample' as COL1,'1' AS COL2,3 as 'RN','2016-05-09 02:47:01.831128' AS Start,'2016-05-09 02:48:55.211807' as Stop,114 as TimeDiff_Start_Stop
union
select 'Sample' as COL1,'1' AS COL2,4 as 'RN','2016-05-09 02:48:56.305736' AS Start,'2016-05-09 02:50:06.107262' as Stop,70 as TimeDiff_Start_Stop
union
select 'Sample' as COL1,'1' AS COL2,5 as 'RN','2016-05-09 02:50:09.269354' AS Start,'2016-05-09 02:50:16.081159' as Stop,7 as TimeDiff_Start_Stop
union
select 'Sample' as COL1,'1' AS COL2,6 as 'RN','2016-05-09 02:50:24.819440' AS Start,'2016-05-09 02:51:04.736300' as Stop,40 as TimeDiff_Start_Stop
union
select 'Sample' as COL1,'1' AS COL2,7 as 'RN','2016-05-09 02:51:41.029165' AS Start,'2016-05-09 02:54:04.186215' as Stop,143 as TimeDiff_Start_Stop
union
select 'Sample' as COL1,'1' AS COL2,8 as 'RN','2016-05-09 02:54:24.537167' AS Start,'2016-05-09 02:55:26.926029' as Stop,62 as TimeDiff_Start_Stop

Output:

select 'Sample' as COL1,'1' AS COL2,'2016-05-09 02:45:18.239669' AS Start,'2016-05-09 02:51:04.736300' as Stop,319 as Time
union
select 'Sample' as COL1,'1' AS COL2,'2016-05-09 02:51:41.029165' AS Start,'2016-05-09 02:55:26.926029' as Stop,205 as Time

Answer 1

Here are 2 different approaches - for this, I created a table @t and used your sample data to populate it - thanks for proving those. Here's the table definition:

declare @t table (col1 varchar(10), col2 int, rn int, start datetime2, stop datetime2, timediff_start_stop int)
insert into @t ... (from the OP)

Here's an approach using a CTE. First, it creates a CTE that just adds the diff (using LEAD to get the difference between current row stop/next row start) as a column.

Note the isnull statement in the CTE query gives a value of 26 to a null if there's no following row - this means the last row in the result set with get a value of 26 (> 25, so will match the criteria of a row that should be an end time in the result set).

;with tdiff (col1, col2, rn, start, stop, timediff_start_stop, diff, timediff)
as
(
select col1, col2, rn, start, stop, timediff_start_stop, isnull(datediff(ss, stop, lead(start) over (order by rn)), 26) as diff, datediff(ss, start, stop)
from @t
)
select t1.col1, t1.col2, t1.start, (select min(stop) from tdiff where stop > t1.start and diff > 25) as stop, 
    (select sum(timediff_start_stop) from tdiff where start >= t1.start and stop <= (select min(stop) from tdiff where stop > t1.start and diff > 25)) AS TIME
from tdiff t1
left join tdiff t2 on (t1.rn - 1) = t2.rn
where t1.rn = 1 or t2.diff > 25

Next, here's a completely different solution using cursors. Cursors are inefficient and not well suited to frequently-run tasks, but I find them easy to maintain and follow as a developer, and think it may be useful for some who need to run infrequent or one-time tasks that are suitable for cursors:

declare @outputtable table (start datetime, stop datetime)
declare @curstart datetime, @curstop datetime, @curdiff int
declare @outputstart datetime

DECLARE cur CURSOR FOR   
select start, stop, datediff(ss, stop, LEAD(start) over (order by rn))
from @t
OPEN cur  

FETCH NEXT FROM cur   
INTO @curstart, @curstop, @curdiff 

WHILE @@FETCH_STATUS = 0  
BEGIN       
    if (@outputstart is null)
        set @outputstart = @curstart

    if (@curdiff > 25)
    begin
        insert into @outputtable values (@outputstart, @curstop)
        set @outputstart = null
    end

    FETCH NEXT FROM cur   
    INTO @curstart, @curstop, @curdiff
END   
CLOSE cur;  
DEALLOCATE cur;  

insert into @outputtable values (@outputstart, @curstop)

select * from @outputtable