By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select *
into t
from (values ('A', convert(datetime, '2018-12-05 08:30:00'), convert(datetime, '2018-12-05 09:45:00')),
('B', convert(datetime, '2018-12-05 09:30:00'), convert(datetime, '2018-12-05 10:00:00')),
('B', convert(datetime, '2018-12-05 10:30:00'), convert(datetime, '2018-12-05 10:45:00')),
('A', convert(datetime, '2018-12-05 10:45:00'), convert(datetime, '2018-12-05 11:45:00')),
('A', convert(datetime, '2018-12-05 12:15:00'), convert(datetime, '2018-12-05 12:30:00')),
('A', convert(datetime, '2018-12-06 05:30:00'), convert(datetime, '2018-12-06 06:45:00')),
('B', convert(datetime, '2018-12-06 06:45:00'), convert(datetime, '2018-12-06 07:45:00')),
('B', convert(datetime, '2018-12-06 08:45:00'), convert(datetime, '2018-12-06 09:30:00'))
) v(Usr, Start_Time, End_Time)
8 rows affected
select t.usr, convert(date, start_time),
avg(datediff(minute, end_time, next_start_time))
from (select t.*,
lead(start_time) over (partition by usr, convert(date, start_time) order by start_time) as next_start_time
from t
) t
group by usr, convert(date, start_time)
order by 2, 1
| usr | (No column name) | (No column name) |
|---|---|---|
| A | 2018-12-05 | 45 |
| B | 2018-12-05 | 30 |
| A | 2018-12-06 | null |
| B | 2018-12-06 | 60 |
Warning: Null value is eliminated by an aggregate or other SET operation.