How can I track the last location of a shipment effeciently using latest date of reporting?

Question

I need to find the latest location of each cargo item in a consignment. We mostly do this by looking at the route selected for a consignment and then finding the latest (max) time entered against nodes of this route. For example if a route has 5 nodes and we have entered timings against first 3 nodes, then the latest timing (max time) will tell us its location among the 3 nodes.
I am really stuck on this query regarding performance issues. Even on few hundred rows, it takes more than 2 minutes. Please suggest how can I improve this query or any alternative approach I should acquire?

Note: ATA= Actual Time of Arrival and ATD = Actual Time of Departure

SELECT DISTINCT 
       c.id   as cid
     , c.ref  as cons_ref 
     , c.Name
     , c.CustRef  
  FROM consignments c  
 INNER JOIN routes r        ON c.Route = r.ID   
 INNER JOIN routes_nodes rn ON rn.Route = r.ID   
 INNER JOIN cargo_timing ct ON c.ID=ct.ConsignmentID   
 INNER JOIN ( SELECT t.ConsignmentID, Max(t.firstata) as MaxDate 
                FROM cargo_timing t 
               GROUP BY t.ConsignmentID 
            ) as TMax   
         ON (    TMax.MaxDate=ct.firstata 
             AND TMax.ConsignmentID=c.ID  
            )
INNER JOIN nodes an         ON ct.routenodeid = an.ID    
INNER JOIN contract cor     ON cor.ID = c.Contract   
WHERE c.Type = 'Road' 
  AND ( c.ATD = 0 AND c.ATA != 0 )   
  AND (cor.contract_reference in  ('Generic','BP001','020-543-912'))   
ORDER BY c.ref ASC    

Answer 1

Can you assign each node an ID and add that ID to the table consignments as a foreign key? That would allow you to set the "current" node when the cargo passes through a node. The time difference between now and the last update of the "current node" field in the consignments table would then give you an idea how for the cargo got on the route.