I have data that looks like:
x
User Start_Time End_Time
-------------------------------------------------
A 2018-12-05 08:30:00 2018-12-05 09:45:00
B 2018-12-05 09:30:00 2018-12-05 10:00:00
B 2018-12-05 10:30:00 2018-12-05 10:45:00
A 2018-12-05 10:45:00 2018-12-05 11:45:00
A 2018-12-05 12:15:00 2018-12-05 12:30:00
A 2018-12-06 05:30:00 2018-12-05 06:45:00
B 2018-12-06 06:45:00 2018-12-05 07:45:00
B 2018-12-06 08:45:00 2018-12-05 09:30:00
I am trying to calculate the average length of time between the End_Time of one entry and the Start_time of the next entry, grouped by the user and the date.
From the example above the output should look like:
User Date Average Time Between Entries(in minutes)
------------------------------------------------------------
A 2018-12-05 45
B 2018-12-05 30
A 2018-12-06 Null
B 2018-12-06 60
Is there a SQL Server query that could accomplish this?
Advertisement
Answer
Use lead()
:
select t.user, convert(date, start_time),
avg(datediff(minute, end_time, next_start_time))
from (select t.*,
lead(start_time) over (partition by user, convert(date, start_time) order by start_time) as next_start_time
from t
) t
group by user, convert(date, start_time);
Here is a db<>fiddle (with the data fixed so the end times are greater than the start times).