I want to calculate the time spent on each status for each driver. I have already calculated the time difference but it is stored in the wrong cell against the wrong driver. Please see the data and my approach so far. Thanks in advance

My Data

Output I want

select 
id,
driver_id,
driver_status,
driver_status.entry_time as cur_time,
LEAD(driver_status.entry_time) over (order by driver_status.id)  as next_time,
DATEDIFF(minute,driver_status.entry_time,lead(driver_status.entry_time) over (order by id))  as timespent in minutes
from driver_status;

CodePudding user response：

Your calls to the LEAD() analytic function need a PARTITION clause:

SELECT
    id,
    driver_id,
    driver_status,
    entry_time AS cur_time,
    LEAD(entry_time) OVER (PARTITION BY driver_id ORDER BY entry_time) AS next_time,
    DATEDIFF(minute,
             entry_time,
             LEAD(entry_time) OVER (PARTITION BY driver_id ORDER BY entry_time)
    ) AS [timespent in minutes]
FROM driver_status
ORDER BY
    driver_id,
    entry_time;