create table profitrides(
ID nvarchar(10)
,START_TIME time
,END_TIME time
,START_LOC nvarchar(1)
,END_LOC nvarchar(1))
insert into profitrides values
('dri_1','9:00:00','9:30:00', 'a','b')
,('dri_1','9:30:00','10:30:00', 'b','c')
,('dri_1','11:00:00','11:30:00', 'd','e')
,('dri_1','12:00:00','12:30:00', 'f','g')
,('dri_1','13:30:00','14:30:00', 'c','h')
,('dri_2','12:15:00','12:30:00', 'f','g')
,('dri_2','12:30:00','14:30:00', 'c','h');
with cte as(
select *
, case when start_time=lag(end_time,1)over(order by start_time) and
start_loc=lag(end_loc,1)over(order by start_time) then 1 end as profit
from profitrides)
select * from cte
Telegram: DB developers channel