Amount lines for the current and next line

Question

I have an input in the following format:

I need to find the difference between stopping the current line and the beginning of the next line, and if the difference is less than 25, I need to sum the values ​​in [TimeDiff_Start_Stop]. If the difference is greater than 25, I do not need to make an amount.

As in the above image, the 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, so only [TimeDiff_Start_Stop] of the first 6 lines is summed, creating line 1 on the output.

The further difference between Stop of Row 7 and Start Row 8 is 20, so [TimeDiff_Start_Stop] from line 7 and line 8 is summed, producing line 2 at the output.

Required conclusion

How do I achieve this?

The following are input and output scenarios:

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 two different approaches: for this, I created the @t table and used your sample data to populate it - thanks for that. Here is 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) 

This uses the CTE approach. First, it creates a CTE that simply adds diff (using LEAD to get the difference between the current line stop / start of the next line) as a column.

Please note that the isnull statement in the CTE request gives a value of 26 for null, if there is no next line - this means that the last line in the result set with a value of 26 (> 25, therefore the criteria that should be the end time in the set will meet the criteria results).

 ;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 

Further, here is a completely different solution using cursors. Cursors are inefficient and not very well suited for frequently performed tasks, but I find them easy to maintain and follow as a developer, and I think that this can be useful for those who need to run rare or one-time tasks 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