Roll up data using the time difference between contiguous rows

WITH CTE AS
 (
	SELECT *,(Row_Number() OVER (PARTITION BY ModelKey ORDER BY StartTime)) as RowNum
	--, CASE WHEN TotalSeconds > 600 THEN 0 ELSE 1 END as SameOutage
	FROM SampleData
	WHERE ModelKey = 8526 
 )
 
,CTERowNum AS
( 
SELECT X.*, ABS(DATEDIFF(SS,EndTime,NxtStartTime)) as Diff
, CASE WHEN ABS(DATEDIFF(SS,EndTime,NxtStartTime)) < 600 THEN 1 ELSE 0 END as SameOutage
FROM
	(
SELECT C.RowId,C.ModelKey, C.StartTime, C.EndTime,C.RowNum ,C2.StartTime as NxtStartTime, C2.EndTime as NxtEndTime,C2.RowNum as NxtRowNum
FROM CTE C
LEFT JOIN CTE C2 
ON C.ModelKey = C2.ModelKey AND C.RowNum = C2.RowNum-1 and C.EndTime < C2.StartTime
	)X
)

SELECT MIN(RowId) as Id,ModelKey, MIN(StartTime) as StartTime, Min(TotalSec),
CASE WHEN MIN(TotalSec) > 600 THEN MAX(EndTime) ELSE MAX(NxtEndTime) END as NxtEnd, MIN(MinRow) as MinRow, MAX(MaxRow) as MaxRow
FROM
(
SELECT RowId,ModelKey,StartTime,EndTime,NxtStartTime,NxtEndTime, ABS(DATEDIFF(SS,NxtStartTime,EndTime)) as TotalSec
, (select TOP 1 RowId FROM CTERowNum C2 WHERE C2.ModelKey = C.ModelKey and isnull(C2.SameOutage,0)<>1 and C2.RowNum < C.RowNum Order by RowId desc) as MinRow
, (select TOP 1 RowId FROM CTERowNum C2 WHERE C2.ModelKey = C.ModelKey and isnull(C2.SameOutage,0)<>1 and C2.RowNum > C.RowNum Order by RowId asc)-1 as MaxRow
FROM CTERowNum C
)X
GROUP BY ModelKey,MinRow,MaxRow
Order by StartTime

Select allOpen in new window

SET NOCOUNT ON;

DECLARE @ID0		INT,
	@StartTime0		DATETIME,
	@EndTime0		DATETIME,
	@IDPrev			INT			= NULL,
	@StartTimePrev	DATETIME	= NULL,
	@EndTimePrev	DATETIME	= NULL,
	@IDCurr			INT			= NULL,
	@StartTimeCurr	DATETIME	= NULL,
	@EndTimeCurr	DATETIME	= NULL,
	@Interval		INT,		--Interval Threshold in seconds
	@RowCount		INT
	
DECLARE @CondensedEvents TABLE
(
	ID				INT,
	StartTime		DATETIME,
	EndTime			DATETIME
	--Include any other columns here
)

SELECT @Interval = 600			--10 minutes

DECLARE crsEvent CURSOR FOR
SELECT ID, StartTime, EndTime
FROM myEvent
ORDER BY ID, EndTime

OPEN crsEvent

FETCH NEXT FROM crsEvent
INTO @ID0, @StartTime0, @EndTime0

WHILE @@FETCH_STATUS = 0
BEGIN
	IF @IDCurr IS NULL
	BEGIN
		PRINT 'No Current Record'
		FETCH NEXT FROM crsEvent
		INTO @IDCurr, @StartTimeCurr, @EndTimeCurr
		
	END
	
	PRINT '0 - ID[' + ISNULL(CAST(@ID0 AS VARCHAR), '') + '] StartTime[' + ISNULL(CONVERT(VARCHAR, @StartTime0, 120), '') + '] EndTime[' + ISNULL(CONVERT(VARCHAR, @EndTime0, 120), '') + ']'
	PRINT 'P - ID[' + ISNULL(CAST(@IDPrev AS VARCHAR), '') + '] StartTime[' + ISNULL(CONVERT(VARCHAR, @StartTimePrev, 120), '') + '] EndTime[' + ISNULL(CONVERT(VARCHAR, @EndTimePrev, 120), '') + ']'
	PRINT 'C - ID[' + ISNULL(CAST(@IDCurr AS VARCHAR), '') + '] StartTime[' + ISNULL(CONVERT(VARCHAR, @StartTimeCurr, 120), '') + '] EndTime[' + ISNULL(CONVERT(VARCHAR, @EndTimeCurr, 120), '') + ']'

	IF @ID0 = @IDCurr
	BEGIN
		--PRINT 'Same ID Value: ' + CAST(@IDCurr AS VARCHAR)
		
		IF DATEDIFF(SECOND, @EndTime0, @EndTimeCurr) > @Interval
		BEGIN
			PRINT 'Interval Exceeded[' + CAST(DATEDIFF(SECOND, @EndTime0, @EndTimeCurr) AS VARCHAR) + ']'
			--Record the condensed record
			PRINT 'Inserting record matching: ID[' + CAST(ISNULL(@IDPrev, @IDCurr) AS VARCHAR) +
				'] StartTime[' + CONVERT(VARCHAR, ISNULL(@StartTimePrev, @StartTimeCurr), 120) +
				'] EndTime[' + CONVERT(VARCHAR, ISNULL(@EndTimePrev, @EndTimeCurr), 120) + ']'
			INSERT INTO @CondensedEvents
			(
				ID,
				StartTime,
				EndTime
			)
			SELECT ID, StartTime, EndTime --Other columns
			FROM myEvent
			WHERE ID = ISNULL(@IDPrev, @IDCurr)
				AND StartTime = ISNULL(@StartTimePrev, @StartTimeCurr)
				AND EndTime = ISNULL(@EndTimePrev, @EndTimeCurr)

			SET @RowCount = @@ROWCOUNT
			IF @RowCount = 0		--This shouldn't happen but I had a bug I had to track down
			BEGIN
				PRINT 'NO MATCHING RECORD FOUND'
			END
			
			SELECT @ID0 = @IDCurr,
				@StartTime0 = @StartTimeCurr,
				@EndTime0 = @EndTimeCurr
		END
		ELSE	--Still within range of interval
		BEGIN
			PRINT 'Interval NOT Exceeded'
			SELECT @IDPrev = @IDCurr,
				@StartTimePrev = @StartTimeCurr,
				@EndTimePrev = @EndTimeCurr
		END
	END
	ELSE	--Reset everything for the new ID value
	BEGIN
		SELECT @ID0 = @IDCurr,
			@StartTime0 = @StartTimeCurr,
			@EndTime0 = @EndTimeCurr,
			@IDPrev = NULL, @StartTimePrev = NULL, @EndTimePrev = NULL,
			@IDCurr = NULL, @StartTimeCurr = NULL, @EndTimeCurr = NULL
	END
	
	--Get the next record
	FETCH NEXT FROM crsEvent
	INTO @IDCurr, @StartTimeCurr, @EndTimeCurr
	
	PRINT ''
END
CLOSE crsEvent
DEALLOCATE crsEvent

SELECT * FROM myEvent WHERE ID = 8526
SELECT * FROM @CondensedEvents

Select allOpen in new window