Subquery with Date Criteria (1 Viewer)

[Josef P.]

Presumably, you're looking for something like this:

SELECT
    V.ID AS VehicleID,
    T.DateVal AS TripDate,
    V.REGN_No,
    T.ID AS TripID,
    T.Place_Travelled,
    (  
        Select Top 1 X.Fuel_Closing 
        from Trips as X 
        where X.Vehicle = T.Vehicle and X.Fuel_Closing < T.Fuel_Closing 
        Order by Fuel_Closing desc 
     ) AS Trip_Opening,
    T.Fuel_Closing AS Trip_Closing
FROM
    Vehicles as V
    INNER JOIN
    Trips as T ON T.Vehicle = V.ID
ORDER BY
    V.ID, T.DateVal, T.ID

Note: if you use an active DBMS like SQL Server, there are simpler variants than a subselect.

 

[prabha_friend]

Previous End is the New Beginning