February 15, 2010 at 10:48 am
I created a view for a database tracking vehicle info that appeared to work ok until we discovered that for several vehicles the mileage has incorrect readings that are larger than the actual current mileage (bunk data, unpredictable as to when it would happen again).
So, when pulling max(mileage), I get the incorrect reading for that particular vehicle because the max is not the most current. Even looking for the most current date of record doesn't appear to get the appropriate record.
Playing around with sql I discovered that if I know ahead of time the vehicleid I always get the correct mileage by using top 1 mileage from table where vehicleid = 'some number'.
However, I don't need only one record. I need the most recent mileage for every vehicle in the database. I'm trying to think of the most appropriate, efficient way to get the correct, most recent mileage for all vehicles. I've considered and played with correlated queries but being new to that couldn't get it to work as I hoped. I'm looking into creating a temp table now but still unsure if it is the best approach.
This is the line that brings me the correct mileage:
SELECT top 1 Odometer,RPTUTCDATE,VehicleName as UnitNumber, VehicleColor as MedicNumber FROM VehicleHistory INNER JOIN Vehicle on Vehicle.VehicleID = VehicleHistory.VehicleID WHERE VehicleHistory.VehicleID = '17' and (Odometer IS NOT NULL) ORDER BY RPTUTCDATE DESC
It outputs one record only. I need this result but for every single vehicle. I have tried leaving out the vehicleid in the where clause but as you can guess, it still returns one record, seeing as how it is top 1. LOL
This query almost always gets me the correct result except when the mileage data is bunk and a greater mileage is actually not the most recent record.
SELECT vh.VehicleID, MAX(vh.RPTUTCDATE) AS DateMileage, v.VehicleName AS UnitNumber, v.VehicleColor AS MedicNumber,vh.Odometer AS Mileage
FROM dbo.VehicleHistory AS vh INNER JOIN
dbo.Vehicle AS v ON vh.VehicleID = v.VehicleID
WHERE (v.VehicleGroupID = 35)
GROUP BY vh.VehicleID, v.VehicleName, v.VehicleColor, Mileage
UNION ALL
SELECT vehicle_id, dt_mileage, unit_number, medic_number, mileage
FROM intranet.dbo.Oilchange_Manual_Entries
This query is only like the first 1/4 of the end result that I actually need, but having the mileage is the cornerstone of everything I am building. Without the most recent data, everything else is incorrect.
If you can lead me in the right direction as far as a new method to try or something that will work in t-sql to give me the most recent mileage for every single vehicle rather than just one, I would be appreciative.
February 15, 2010 at 11:20 am
Roughly, because there's no table def or sample data that I can use to test...
SELECT Odometer, RPTUTCDATE, UnitNumber, MedicNumber
FROM (
SELECT Odometer,
RPTUTCDATE,
VehicleName as UnitNumber,
VehicleColor as MedicNumber,
ROW_NUMBER() OVER (PARTITION BY Vehicle.VehicleID ORDER BY RPTUTCDATE DESC) AS RowNo
FROM VehicleHistory INNER JOIN Vehicle on Vehicle.VehicleID = VehicleHistory.VehicleID
WHERE Odometer IS NOT NULL ) sub
WHERE sub.RowNo = 1
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 15, 2010 at 11:25 am
Is the following an accurate statement?
DateMileagev.VehicleName AS UnitNumberv.VehicleColor AS MedicNumberMileage
'1/1/2010''Henderson' (name not Number) 'Red'Color but not Number 106230
'12/17/2009''Henderson' 'Red'119221
Data from a previous date may have a higher Odometer value than the most recent date? Also, VehicleName indicates characters to me and not a number - are the vehiclenames truly numbers?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 16, 2010 at 7:29 am
Thanks. I will test this and if magic doesn't occur immediately I will post table def and sample data. I appreciate the help.
February 16, 2010 at 7:30 am
Yes, we took several fields from one system that were not being used so we could link tables from a diff system that ID's vehicles in a totally diff way.
VehicleColor and VehicleName are actually used to tell us different information.
February 16, 2010 at 10:23 am
Then you will need to add odometer reading to the query that Gail provided.
ROW_NUMBER() OVER (PARTITION BY Vehicle.VehicleID ORDER BY Odometer Desc, RPTUTCDATE DESC) AS RowNo
This is based on the understanding that you would want the highest odometer reading and it's associated date.
Since the data is coming from different systems, that explains a little bit the inconsistency. Getting the data consistent would help create accurate results and simplify your queries.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 16, 2010 at 11:01 am
Gail - so far the query you provided checks out. I'm waiting back to hear from the other department to see if the results are what they would expect to see.
I appreciate everyone's help.
February 16, 2010 at 11:06 am
You're welcome.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply