How can I track the last location of a shipment effeciently using latest date of reporting?
- by hash
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
