I have a table of vehicle usage records with fields including vehicle number, driver, date/time, starting odo, ending odo. I'm looking to compare the starting odo of a given record to the ending odo of the last time that vehicle was used. I also need to return other fields from that previous record, like the date/time and driver.
My users basically run a report for any given day, and the vehicles used on that day need to be compared to their most recent usage (most recent relative to the record at hand). This is to ensure that the vehicle hasn't been used in the interim and not recorded (which may indicate theft).
I've got a very convoluted process in place, but I'd like to see if it can be streamlined. The current process is done in Access and has a number of queries built on other queries. It is very slow. The data is in SQL Server. Any thoughts on the best ways to accomplish this?
I'd find the previous odometer reading for this vehicle by finding the largest odometer reading for this vehicle that was less than the current odometer reading. The exact syntax is a bit tough for me to devine without access to your schema!
Thanks for that Pat, but it's a little more basic than I need. As noted, I also need to return other fields from that previous record (so the users can research any differences found-the problem is sometimes with the older record). I came up with a solution last night that seems to be working in my early testing this morning. In an SP, I first create a temp table of the vehicle trips for the selected day. As part of that table, I added a calculated field that's basically:
(SELECT Max(DateTimeField) FROM TableName WHERE TableName.CarNum = TempTable.CarNum and TableName.DateTimeField < TempTable.DateTimeField) AS PrevRecord
Then I have a select statement that joins the temp table to the original table on both car number and this calculated field. That enables me to select the needed fields from both the current record and the previous record.
This may not be the best solution; your knowledge of SQL is far greater than mine. However, it is MUCH faster than the pile of Access queries it will replace.
I apologize if I should have included the actual DDL of the table. I was really just after a conceptual direction rather than an answer on a silver platter, so I didn't think it necessary. In retrospect I fell victim to the "I see it here in front of me so I don't really need to show it to you" problem that posters so often seem to have.
Just an observation, but using the schema you've presented in your example, I'd suggest trying:
FROM TableName AS c
LEFT JOIN TableName AS p
ON (p.CarNum = c.CarNum
AND p.DateTimeField = (SELECT Max(z.DateTimeField)
FROM TableName AS z
WHERE z.CarNum = c.CarNum
AND z.DateTimeField < c.DateTimeField))
This should work pretty well, especially if you put an index on CarNum then DateTimeField to give the optimizer a boost.
The C alias is the current data, the P alias is the previous data, the Z alias is just a lookup to find the appropriate DateTimeValue.
Ah, thus eliminating the temp table. That looks great. I'm working on an ASP/SQL Server project now (I'm a "jack-of-all-trades, master-of-none"), but I'll take a look at this later tonight. Thanks very much for taking the time to work it out for me.