CODEWITHSUNDEEP
sql
Neeraj
Neeraj

Reputation: 1

I have multiple rows in a select command which are filled with events. now I want to calculate the time between the events:

SELECT Trip.tripId
    ,Trip.vehicleid
    ,Trip.deviceid
    ,Trip.location
    ,Trip.event
    ,Trip.officeid
    ,Trip.eventdate
    ,IIf([PreviousDate] IS NULL, 0, + CAST(DateDiff(minute, [PreviousDate], [eventdate]) % 60 AS VARCHAR(100)) + 'min' + CAST(DateDiff(second, [PreviousDate], [eventdate]) % 60 AS VARCHAR(100)) + 'sec') AS [Deviation Time]
FROM (
    SELECT tripId
        ,vehicleid
        ,deviceid
        ,location
        ,officeid
        ,event
        ,eventdate
        ,(
            SELECT Max(eventdate)
            FROM Trip AS T2
            WHERE
                --T2.location=T1.location and
                --T2.event=T1.event and
                --  and
                T2.eventdate < T1.eventdate
                AND T2.tripId = T1.tripId
            ) AS PreviousDate
    FROM Trip AS T1
    ) AS Trip
WHERE officeid = 355
    AND vehicleid = 8306
    AND event LIKE '%deviation%'
ORDER BY tripId
    ,eventdate

Upvotes: 0

Views: 16

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270623

By far the simplest method is to use the ANSI standard function lag():

select t.*,
       datediff(minute,
                lag(eventdate) over (partition by eventid order by eventdate),
                eventdate
               ) as timediff_minutes
from trip t;

I don't fully understand how you want to format the difference, but the simplest way to get the values is using window functions.

Upvotes: 1

Related Questions