L' Eomot Inversé (11/5/2013)
You need some additional bchages as well as Dwain's correction, in order to handle the hvehicle history correctly.First remove
INNER JOIN History H ON H.VehicleID=V.VehicleID
(actually since the Vehicles table isn't needed for this query you should remove
INNER JOIN Vehicles AS V
as well.)
Then change the SELECT line to
SELECT (SELECT DriverName from History H
WHERE H.ReceivedDate = (SELECT MAX(ReceivedDate) from History H2 where H2.VehicleID = R.VehicleID and H2.ReceivedDate <= R. RepairDate)
AND H.VehicleID = R.VehicleID) AS DriverName, R.RepairCost, R.RepairDate
I haven't checked that this works with your data because I can't get the data to load, but it should show you what needs doing even if it doesn't work - you need to get who owned the vehicle at the repair date, by looking for the latest history date for the vehicle which isn't after the repair date and then extracting the driver name from the history record with that history date and vehicleID.
Tom has hit it on the nose. It's difficult to provide good help because you should help us by providing DDL, consumable sample data and expected results. From that, it should be short work for someone to provide you with a working solution.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St