'Calculate time Duration MySQL

Trying to calculate the time duration based on the event type. Specifically, trying to find the time on Pre-trip_Sumbit and Pre_Trip_Start. Sample Time

Here is a test query that I am getting an error. No need to worry about join.

SELECT ROUTE_NUM, TRUCK_NUM, 
    DATEDIFF(
    (select EVENT_TIME_LOCAL where EVENT_TYPE = 'VCR_PRE_TRIP_START') ,
    (select EVENT_TIME_LOCAL where EVENT_TYPE = 'VCR_PRE_TRIP_SUBMIT')) as Time_Duration
from ACTIVE_ROUTE_HEADER_HISTORY as arh
inner join EVENT_HISTORY as eve on arh.PK_ACTIVE_ROUTE_HEADER_ID = eve.XPK_ACTIVE_ROUTE_HEADER_ID
where ROUTE_DATE = '2022-03-17';

Error Message:

SQL Error [1064] [42000]: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'where EVENT_TYPE = 'VCR_PRE_TRIP_START') ,
    (select EVENT_TIME_LOCAL where EVEN' at line 3
mysql


Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source

Related Questions