SQL Server datetime ranges between records

Question

What would be the best way to get datetime ranges between records in SQL Server? I think it would be easiest to explain with an example.

I have the following data - these records start and end datetime ranges would never overlap:

ID	Start	End
1	1/27/2021 06:00:00	1/27/2021 09:00:00
2	1/27/2021 10:00:00	1/27/2021 14:00:00
3	1/27/2021 21:00:00	1/28/2021 04:00:00
4	1/28/2021 06:00:00	1/28/2021 09:00:00

question from:https://stackoverflow.com/questions/65929834/sql-server-datetime-ranges-between-records

Answer 1

Use lead():

select t.*
from (select id, end as start, lead(start) over (order by start) as end
      from t
     ) t
where end is not null;

Note: end is a lousy name for a column, given that it is a SQL keyword. I assume it is for illustrative purposes only.

Here is a SQL Fiddle.