By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table t
(
ID INT,
LOCATION VARCHAR(10),
DT DATETIME
)
insert into T VALUES
(1,'ED','01/01/2019 12:01' ),
(2,'ED','01/01/2019 02:01' ),
(3,'ED','01/01/2019 05:01' ),
(4,'AB','01/02/2019 02:01' ),
(5,'AB','01/03/2019 02:01' ),
(6,'ED','01/03/2019 19:01' ),
(7,'ED','01/04/2019 02:01' )
7 rows affected
select location, min(dt), max(coalesce(next_dt, dt))
from (select t.*, lead(dt) over (order by dt) as next_dt,
row_number() over (partition by location order by dt) as seqnum_l,
row_number() over (order by dt) as seqnum
from t
) t
group by location, (seqnum - seqnum_l)
order by min(dt)
| location | (No column name) | (No column name) |
|---|---|---|
| ED | 01/01/2019 02:01:00 | 02/01/2019 02:01:00 |
| AB | 02/01/2019 02:01:00 | 03/01/2019 19:01:00 |
| ED | 03/01/2019 19:01:00 | 04/01/2019 02:01:00 |