Hi,
RANK()
might be useful in this scenario.
Please try below query..
SELECT
dbo.Owners.HaulierId,
dbo.Vehicles.VehicleId,
dbo.Vehicles.Code,
dbo.Owners.Name,
dbo.VehicleOwnershipHistory.OwnedFromDate,
RANK() over(partition by dbo.VehicleOwnershipHistory.OwnedFromDate order by dbo.Owners.HaulierId) Rnk
FROM dbo.VehicleOwnershipHistory
INNER JOIN dbo.Vehicles
ON dbo.VehicleOwnershipHistory.VehicleId = dbo.Vehicles.VehicleId
INNER JOIN dbo.Haulier
ON dbo.VehicleOwnershipHistory.OwnerId = dbo.Owner.HaulierId
WHERE dbo.VehicleOwnershipHistory.OwnedFromDate <= @Date
and Rnk = 1
ORDER BY dbo.VehicleOwnershipHistory.OwnedFromDate DESC
From your query i have removed
top 1
and used
RANK() over(partition by dbo.VehicleOwnershipHistory.OwnedFromDate order by dbo.Owners.HaulierId) Rnk
and put a condition to fetch the data with only First Rank
Where Rnk = 1
Hope this will help..