Calculate Vehicle Engine distance, duration with respect to start and end time

Question

Calculate Engine start time, end time, distance, duration for the vehicle

CREATE TABLE Vehicle(Veh varchar(10), VDatetime datetime2, Eventtype varchar(10), Odometer int)

INSERT INTO Vehicle(Veh,VDatetime,Eventtype,Odometer)

VALUES('V1', '2025-01-01 10 AM', 'ign on', 100),  
 ('V1', '2025-01-01 01 PM', 'ign off', 200),

  ('V1', '2025-01-01 04 PM', 'ign on', 200),

  ('V1', '2025-01-01 10 PM', 'ign off', 300)

  SELECT * FROM Vehicle
```-- Calculate Engine start time, end time, distance, duration for the vehicle

-- Output shoud be as below

vechicle	datetimefrom	datetimeto		distance		duration

V1				10 AM			1 PM			100			  3 HRS

V1				4 AM			10 PM			100			  6 HRS

Answer 1

Try this too:

select a.Veh, format(a.VDatetime, 'h tt') as datetimefrom, format(b.VDatetime, 'h tt') as datetimeto, 
	b.Odometer - a.Odometer as distance, concat( datediff(hour, a.VDatetime, b.VDatetime), ' HRS') as duration
from Vehicle a
inner join Vehicle b on b.Veh = a.Veh and b.Eventtype = 'ign off' and b.VDatetime > a.VDatetime
left join Vehicle c on c.Veh = a.Veh and c.VDatetime > a.VDatetime and c.VDatetime < b.VDatetime
where a.Eventtype = 'ign on'
and c.Veh is null
order by Veh, datetimefrom

Answer 2

I was trying myself on this solution. Got something as below. But still start and end period in hour AM/PM is incomplete. Below is the CTE query that can achieved this one.

WITH d_on AS
(
    SELECT  d.Veh, d.VDatetime, Odometer,eventtype
    FROM    Vehicle d 
    WHERE   d.eventtype = 'ign on'
),
next_on AS
(
    SELECT  d_on.Veh,
            d_on.VDatetime,
			d_on.Odometer,
            LEAD(d_on.VDatetime, 1, '99991231') OVER (PARTITION BY d_on.Veh ORDER BY d_on.VDatetime) AS nexton
    FROM    d_on
)
SELECT      next_on.Veh,
            next_on.VDatetime AS [datetimestart],
            a.[datetimeend],
			next_on.Odometer AS Odometerstart,
			a.Odometer - next_on.Odometer AS Distance,
			DATEDIFF(Hour,next_on.VDatetime,a.[datetimeend]) Duration
FROM        next_on
            OUTER APPLY
            (
                SELECT  MIN(VDatetime) AS [datetimeend], Odometer
                FROM    Vehicle d_off
                WHERE   d_off.eventtype = 'ign off'
                        AND d_off.Veh = next_on.Veh
                        AND d_off.VDatetime BETWEEN next_on.VDatetime AND next_on.nexton
						group by Odometer
            ) a
ORDER BY    [datetimestart];