time different excluding overlap period

The difference always be the difference between starts of the periods + the difference of the ends of the periods if I understood you correctly.

DECLARE @S1 datetime, @S2 datetime, @E1 datetime, @E2 datetime



SELECT @S1 = StartTime, @E1 = EndTime

FROM @tbl

WHERE id = 1;



SELECT @S2 = StartTime, @E2 = EndTime

FROM @tbl

WHERE id = 2;



SELECT ABS(DATEDIFF(hh,@S1,@S2)) + ABS(DATEDIFF(hh,@E1,@E2))

OR, if you want to know the difference between yours and the next row:

SELECT id, ABS(DATEDIFF(hh,startTime,LEAD(startTime) OVER (ORDER BY id))) + ABS(DATEDIFF(hh,endTime,LEAD(endTime) OVER (ORDER BY id)))

FROM @tbl