Sorry to bother you, I just got it working, not quite
sure about order of INNER JOINs any reference to a
white paper or quick reason why would be good.


SELECT vQMD.FleetNumber,
SC.ID,
TH.Date,
ST1.OffRoad,
getDate()-(ST1.[nextservicedue]) as Overdue
FROM StatusCodes SC
INNER JOIN SQLTrailers ST1
INNER JOIN
(
SELECT DISTINCT ST2.FleetNumber,
max(TH.Date) as MaxOfDate
FROM StatusCodes SC
INNER JOIN SQLTrailers ST2
INNER JOIN TrailerHistory TH
ON ST2.FleetNumber = TH.FleetNumber
ON SC.ID = TH.Status
GROUP BY ST2.FleetNumber
) vQMD
ON ST1.FleetNumber = vQMD.FleetNumber
INNER JOIN TrailerHistory TH
ON ST1.FleetNumber = TH.FleetNumber
ON SC.ID = TH.Status
WHERE TH.Date = vQMD.MaxOfDate
AND ST1.[Group] = 1
ORDER BY TH.FleetNumber